CODEWITHSUNDEEP
sql
Gavrilo Adamovic
Gavrilo Adamovic

Reputation: 2795

Select entries that have exactly specified associations in SQL

If, for example, there is some entity that can have multiple tags, and associations are described using additional table entity_tag which holds entity_id and tag_id, how can I select all entities which have exactly some set of tag_ids?

Basically something like this:

select entity_id 
from entity_tag et 
where (select tag_id 
       from entity_tag
       where entity_id = et.entity_id) = (1, 2, 3)

Upvotes: 0

Views: 51

Answers (1)

Serg
Serg

Reputation: 22811

Find entities where entity_tag contains all 3 tags for an entity, and possibly more.

select entity_id 
from entity_tag 
where tag_id in (1,2,3)
group by entity_id 
having count(distinct tag_id)=3

3 tags exactly

select entity_id
from entity_tag 
group by entity_id 
having count(distinct case when tag_id in (1,2,3) then tag_id else 0 end) = 3
and min(case when tag_id in (1,2,3) then tag_id else 0 end) > 0

Dbfiddle

Upvotes: 2

Related Questions