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 )
    by id1
having count(distinct id2) = number

Neat, eh?

For More Information

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.