Select using a different value than id on join [closed]

Question

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.

Answer 1

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'
    )

Answer 2

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.

Answer 3

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.

Answer 4

SELECT SUM(t1.QUALITY)from t1
LEFT OUTER JOIN t2
ON t2.PRODUCT_ID = T1.PRODUCT_ID
WHERE t2.MATERIAL = 'metal';