My two tables are AC, which contains two columns (A_id,M-id), and DC, which contains only one column (M_id).
Table AC A_id M_id 7 1 7 2 7 3 8 5 8 1 8 3 9 1 9 2 9 3 9 4 9 5 10 1 10 5 Table DC M_id 1 2 3
Now here's the problem. I want to retrieve the A_id's from AC table which contain all the M_id's of DC table. For example, the answer should be:
A_id 7 9
Both of those A_id's contain the M_id values 1,2,3.
Whenever you are looking for an answer which must be obtained from several rows, the query usually involves grouping. In this example, you need to GROUP BY A_id, count the number of joined AC-DC rows, and then check to see that this is the same as the number of rows in DC. The HAVING clause with a subquery is perfect for this.
select AC.A_id from AC inner join DC on AC.M_id = DC.M_id group by AC.A_id having count(*) = ( select count(*) from DC )
Easy when you see it, but not so obvious if you've never seen it before, eh.
This was first published in June 2005