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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.