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.