sql - filter items from a many to many relation table

Question

I have a table 'many-to-many' many_relation with :

• relation_id unique id
• set_id link to another table with sets
• element_id link to another table with elements

So the idea is to have many sets representing 'groups' of elements (1 to many elements)

What I would like to do is to be able to select all the sets (set_id) that include given elements. So for example, if elements are [1,2,3] and sets are [a,b] imagine:

• set 'a' is 'linked' to elements 1 and 2
• set 'b' is 'linked' to elements 1 and 3 then the table many_relation will be:

relation_id	set_id	element_id
11111	a	1
222222	a	2
333333	b	1
444444	b	3

question from:https://stackoverflow.com/questions/65920171/filter-items-from-a-many-to-many-relation-table

Answer 1

You can use group by and having:

select set_id
from many_relation
where element_id in (1, 2)
group by set_id
having count(*) = 2;  -- the "2" is the number of items in the `in` list

EDIT:

In Postgres, you can do this using arrays. For instance:

select set_id
from many_relation
where element_id = any (array[1, 2])
group by set_id
having count(*) = cardinality(array[1, 2]);

This repeat the array for clarity. You can define it using a CTE (for instance). However, I suspect you are passing it in as a parameter, so ? would just be in both places.