How to choose SELECT for the next?

Question

I have a table with two columns of identifiers.

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 matches a specific id2s combination.

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 best to accomplish this would be appreciated.

Thank,

Answer 1

You can find the appropriate id2 identifiers and use count(distinct id2)to verify that all id2 matches. The counter must be equal to id2. For id2 = [1,2,3] the counter should be 3:

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

1. id2 = [1,6]:

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

2. id2 = [1]:

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

1, 2, 3.

Answer 2

Try:

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

:

DISTINCT id1
--------
1