EXPERT RESPONSE
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
|
