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
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.