user7237237 user7237237 - 6 days ago 4
SQL Question

MySQL select if all values belonging to id are present in another table

Basically I have two tables:

rid|element id|element
1 | x 1 | a
1 | y 2 | b
1 | z 3 | c
1 | a 4 | d
2 | b 5 | e
2 | c 6 | x
2 | d 7 | z
3 | e
3 | z
3 | x


and I want to return
rid = 2, 3
but not
1
because the element
y
is not present in the second table. I think the answer lies in the type of join I use but I haven't really been able to wrap my head around those

Answer

I would go with left joining the first table to the second and then aggregating by rid to check that every element from the first table in fact matched to an element in the second table.

SELECT t1.rid
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.element = t2.element
GROUP BY t1.rid
HAVING SUM(CASE WHEN t2.element IS NULL THEN 1 ELSE 0 END) = 0