return IDs that have ALL of a list of correpsonding values

Question

I have table with 2 columns ....

id    id2
1      1
1      2 
1      3 
2      1
2      2
2      4
3      2
3      3
3      4

I want to return the ids which have for example id2 in (1, 2, 4) but that has all of the values in the list.

In this above case it would return id = 2. Is this possible?

Answer 1

select id
from MyTable
where id2 in (1, 2, 4)
group by id
having count(distinct id2) = 3 --this must match the number of elements in IN clause

Update:

If the list of IDs is variable, then you should create an additional table that contains the varying sets of IDs, which you can then JOIN against to do your filtering.

Answer 2

Do a self-join to test different rows on the same table in one go:

SELECT id
FROM t AS t0
JOIN t AS t1 ON t1.id=t0.id
JOIN t AS t2 ON t2.id=t1.id
WHERE t0.id2=1
AND t1.id2=2
AND t2.id2=4

Answer 3

Are you alluding to relational division? e.g. the supplier who supplies all products, the pilot that can fly all the planes in the hanger, etc?

If so, this article has many example implementations in SQL.