Getting SQL rows where >= 1 row have a certain value in another column

Question

I've seen similarly worded questions, and I may be phrasing it wrong, but take the following example table:

a	b
1	5
2	6
3	7
1	8
2	8
2	9
1	10
2	10
3	10

And say I know beforehand that I have a values [1,2]. How can I get all values of b that the a values share? In the above, the result would be [8, 10]. If I had [1,3] for a, then I would get [10]. If I had [2] for a, I would get [6,8,9,10]

I imagine it would start something like SELECT b from tablename WHERE ...

Answer 1

You can use intersect

Schema and insert statements:

 create table test(a int, b int);

 insert into test values(1, 5);
 insert into test values(2, 6);
 insert into test values(3, 7);
 insert into test values(1, 8);
 insert into test values(2, 8);
 insert into test values(2, 9);
 insert into test values(1, 10);
 insert into test values(2, 10);
 insert into test values(3, 10);

Query1: select b from test where a=1 intersect select b from test where a=2

Output:

b
8
10

Query2:

 select b from test where a=1
 intersect 
 select b from test where a=3

Output:

b
10

Query3:

 select b from test where a=2

Output:

b
6
8
9
10

db<>fiddle here

Answer 2

Create a CTE that returns the values of a that you want and filter the table for these values only.
Then group by b and in the HAVING clause filter the resultset so that only values of b that are associated to the values of a that you want are returned:

WITH cte(a) AS (VALUES (1), (2))
SELECT b
FROM tablename
WHERE a IN cte
GROUP BY b
HAVING COUNT(*) = (SELECT COUNT(*) FROM cte);

See the demo.