Jason Vondersmith Jason Vondersmith - 26 days ago 6
SQL Question

Combining 2 SQL queries into 1

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:

enter image description here

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