SQL query where 'IN' act as 'AND' not 'OR'?

Question

Pardon the title, I've no clue what to call this. So imagine I have this

table_ref

id_x|id_y
---------
6|70
6|71
6|72
6|73
8|70
8|73
9|72
9|73

How can i select id_y only if it matches id_x= 6 & 8 & 9? in this case it should return me 73

the returned result of id_y will then be used as an inner join in another sql query.

Answer 1

Of course, it is hard to parametrize, but if it important then you can pass the values as table-valued parameter.

SELECT T.id_y
FROM table_ref T
  JOIN (VALUES (6), (8), (9)) A(id_x)
    ON T.id_x = A.id_x
GROUP BY T.id_y
HAVING COUNT(*) = 3