Jason Vondersmith - 4 months ago 22

SQL Question

I have a complex (for me) query that I can't seem to get right. I can get the data I'm looking for separately but need to combine them in same query. I am not looking to use a view or a temp table.

`SELECT PRODUCT_T.PRODUCTSTANDARDPRICE AS PSTANDARDPRICE`

FROM PRODUCT_T, USES_T

WHERE PRODUCT_T.PRODUCTID = USES_T.PRODUCTID

This query returns:

PSTANDARDPRICE

175

200

200

750

Then I do:

`Select uses_t.productid, sum(rawmaterial_t.materialstandardprice*uses_t.quantityrequired) AS TOTALRAWMATERIALCOST`

From rawmaterial_t, uses_t

Where rawmaterial_t.materialid = uses_t.materialid

Group by uses_t.productid

order by productid

This query returns:

PRODUCTID TOTALRAWMATERIALCOST

1 120

2 62.4

3 307.2

I'm looking for:

I've tried to use a sub query, I've tried to use a Join with the two queries and I've tried a UNION in desperation.

This gets me close but TOTALRAWCOSTPERPRODUCT is the same for every row which is incorrect

`SELECT PRODUCT_T.PRODUCTID, PRODUCT_T.PRODUCTSTANDARDPRICE AS PSTANDARDPRICE, TOTALRAWCOSTPERPRODUCT`

FROM (

Select uses_t.productid, sum(rawmaterial_t.materialstandardprice*uses_t.quantityrequired) AS TOTALRAWCOSTPERPRODUCT

From rawmaterial_t, uses_t

Where rawmaterial_t.materialid = uses_t.materialid

Group by uses_t.productid

), PRODUCT_T, USES_T

WHERE PRODUCT_T.PRODUCTID = USES_T.PRODUCTID

How can I get to where I need to be?

Answer

```
SELECT
uses_t.productid,
PRODUCT_T.PRODUCTSTANDARDPRICE AS PSTANDARDPRICE,
sum(rawmaterial_t.materialstandardprice*uses_t.quantityrequired) AS TOTALRAWMATERIALCOST
FROM
uses_t
INNER JOIN rawmaterial_t
ON rawmaterial_t.materialid = uses_t.materialid
INNER JOIN PRODUCT_T
ON PRODUCT_T.PRODUCTID = USES_T.PRODUCTID
GROPUP BY
uses_t.productid,
PRODUCT_T.PRODUCTSTANDARDPRICE
ORDER BY
uses_t.productid
```