2

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!

2

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

  • Note that the first step above (counting the articles that have 3 tags) assumes that the article_tags table has one and only one record for each tag on each article. If the combination of artid and tagid in article_tags isn't unique, you could get false positives in this step. You can work around this by counting of times each tag appears (SUM(CASE WHEN tagid = 1 THEN 1 ELSE 0 END), etc.) or by pulling DISTINCT artid, tagid into a temp table, and proceeding from there. Also, if article_tags doesn't have a reason to have multiple entries with those column values, try to clear out dupes. – RDFozz Mar 16 '17 at 22:10
  • 1
    @RDFozz There is no table schema and no sample data, I've just added another solution using EXISTS for three articles-tags just to take care of duplicate data. – McNets Mar 16 '17 at 22:14
  • completely understood. Just thought it might be worth noting, in case someone inexperienced might have tried using option 1 and not have recognized the problem. If it wasn't clear, I would prefer to assume the table was nice and clean - I've just run into similar cases where it wasn't. And, yes, the EXISTS absolutely avoids the problem to begin with! – RDFozz Mar 16 '17 at 22:22
  • 2
    @RDFozz I've just changeg the order. May be it's more clear. – McNets Mar 16 '17 at 22:32
  • Thanks for the great answer! Sorry I haven't mentionned: yes the combination of article_id and tag_id in article_tags is unique. One other think I'd like to mention as well is that I have a very restrictive condition on the article in the main query (SELECT * FROM article a WHERE a.col = 'very restrictive value'). Having this in mind, which way do you think has the best performances? I am afraid the subquery would return a lot of rows (a lot of articles have the same combination of tags, but only a few respect my restrictive condition). I'm not sure if it has a real impact or not. Thanks! – abacco Mar 17 '17 at 5:05
0

Another way..

select   artid, GROUP_CONCAT(tag_id ORDER BY tagid) AS tags
    from     articles_tags
    where    tagid in (1,2,3)
    group by artid
    having   tags = "1,2,3";

Tips on an optimal many:many mapping table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.