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, SQL Consultant, r937.com
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue 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.