Q
Problem solve Get help with specific problems with your technologies, process and projects.

Having all values in a list

I have a table that has two ids: id1 and id2, and there are multiple id2's for every id1. I have a list of id2s,...

and I want to write a select statement that will pull the id1s that have a row for each of the id2s in my list. How would I do that?

This question often comes up when trying to find all job candidates who have a certain list of skills, all products that have a certain list of keywords, etc. These situations all share one characteristic: they involve a one-to-many relationship. You're trying to find instances of the "one" value which have every specified instance of the "many" values. Never mind that the "relationship" in this particular situation is within the same table, the principle still holds.

The first thing you do is to set up a basic SELECT for the "many" values which have a value in the list:

select id1
  from yourtable
 where id2 
    in ( list of values )

The above query will give you all id1's which have any of the id2 values in the list. Clearly, you just want the id1's that have all of them.

The trick here is to ask yourself how many id2 values are in the list. Then just put that number into the query as an aggregate condition:

select id1
  from yourtable
 where id2 
    in ( list of values )
group
    by id1
having count(distinct id2) = number

Neat, eh?

For More Information


This was last published in June 2004

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close