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?

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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 first published in June 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top