-2

I have two tables, normalized:

table1
id    quality    product_id 
1     100       1
2     100       2
3     10        4
4     50        2

and

table2
product_id    product    material
1             tinder     metal
2             gold       metal
3             timber     wood
4             stone      rock

How can I get all items on the first table that are metal? I tried

select sum(quality) from table1 join table2 on table2.material = 'metal';

but it seems that is not correct (720), it should be 250.

2
  • select table1.* from table1 join table2 using (product_id) where table2.material = 'metal'; – ypercubeᵀᴹ Jul 29 '15 at 12:31
  • So this should belong to stack overflow? If so, please migrate. – Ivan Jul 29 '15 at 13:44
5

I'm not sure if I understand the relation between the tables but if I did this should work:

SELECT 
    SUM(quality) 
FROM table1 
WHERE product_id IN (
    SELECT 
        product_id 
    FROM table2 
    WHERE material = 'metal'
    )
1

You can make it with a simple JOIN:

SELECT
    SUM(table1.quality) as qlty,
    GROUP_CONCAT(table1.product_id ORDER BY table1.product_id ASC) AS all_products_id_matching
FROM test.table1
JOIN test.table2 ON (table2.product_id=table1.product_id)
WHERE table2.material='metal';

OR a subquery:

SELECT
    SUM(table1.quality) as qlty
FROM test.table1
WHERE table1.product_id IN (SELECT table2.product_id 
                            FROM test.table2 
                            WHERE table2.material='metal');

You can also use JOIN with USING (product_id) or WHERE EXISTS.

0
Select a.*
From table1 a 
Left join table2 b on a.id=b.id
Where b.material="metal"

I'm assuming ID is the common denominator, and not your product id.

2
  • You could also add ,b.material to the select statement to see the materials are metal. – JW2 Jul 29 '15 at 12:29
  • 1
    That should probably be a join on product_id and not id – Tom V Jul 29 '15 at 12:39
-3
SELECT SUM(t1.QUALITY)from t1
LEFT OUTER JOIN t2
ON t2.PRODUCT_ID = T1.PRODUCT_ID
WHERE t2.MATERIAL = 'metal';
1
  • 1
    Why does this query meet the requirements of the question? Please provide more than just code as an answer. – user507 Jul 29 '15 at 13:42

Not the answer you're looking for? Browse other questions tagged or ask your own question.