How to filter a JOIN on *all* items of a given list (filter articles with all given tags for example) I have this use case which seems quite usual but I don't find any efficient and obvious solution.

I have an article table, a tag table and a intermediate article_tag table (each article can have multiple tags, and each tag can be linked to multiple articles).

Given a list of tag ids 1, 2, 3, I want to retrieve articles that have all these tags at the same time.

I easily managed to retrieve articles that have at least one tag in this list:

SELECT * FROM article a
JOIN article_tag at ON at.article_id = a.id AND at.tag_id IN (1, 2, 3)


This query can return articles with only 1 tag (id 2 for example). But this is not what I want. I want only the articles with the 3 exact tags 1, 2 and 3.

How to do it in an efficient way? There are quite a lot of articles, I do care about performances.

Thanks!
IMHO the best way to ensure it is by using EXISTS to check one by one every condition:

select a.*
from   __articles a
where  exists (select 1 from __articles_tags where artid = a.artid and tagid = 1)
and    exists (select 1 from __articles_tags where artid = a.artid and tagid = 2)
and    exists (select 1 from __articles_tags where artid = a.artid and tagid = 3);


Bu depending on your table schema, you could use GROUP BY and COUNT to select which articles have the desired tags.

select   artid
from     articles_tags
where    tagid in (1,2,3)
group by artid
having   count(*) = 3


Then use the result list to fetch articles records.

select  *
from    articles
where   artid in (select   artid
                  from     articles_tags
                  where    tagid in (1,2,3)
                  group by artid
                  having   count(*) = 3);


Check it here: http://rextester.com/LOB71207