How do I SELECT for the following?

Question

I have a table with two columns of ids.

TABLE
id1     id2
1       1
1       2
1       3
2       1
2       6
2       2
3       1
3       10
3       3

I would like to SELECT every id1 that is matched to a certain combination of id2s.

For example, if I have id2 = [1, 2, 3] I would like to return id1 = [1];

If I have id2 = [1, 6] I would like to return id1 = [2];

If I have id2 = [1] I would like to return id1 = [1, 2, 3].

Any suggestions on how to best accomplish this would be appreciated.

Thanks,

Answer 1

Try:

SELECT DISTINCT id1
AS found_id1
FROM table
WHERE id2 IN (1, 2, 3)

You will get:

DISTINCT id1
--------
1

Answer 2

You can search for matching id2's, and use count(distinct id2) to verify that all id2's were matched. The count should be equal to the number of id2's. For id2 = [1,2,3], the count should be 3:

select id1 
from YourTable
where id2 in (1,2,3)
group by id1
having count(distinct id2) = 3;

This prints 1. For id2 = [1,6]:

select id1 
from YourTable
where id2 in (1,6)
group by id1
having count(distinct id2) = 2;

This prints 2. For id2 = [1]:

select id1 
from YourTable
where id2 in (1)
group by id1
having count(distinct id2) = 1;

This prints 1, 2, 3.