Q

Must have all rows

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close