CODEWITHSUNDEEP
sql
me22
me22

Reputation: 641

SQL to find articles with ALL of a set of tags

Finding articles with ANY of a set of tags is a relatively simple join, and has been discussed already: Best DB (MySQL) structure: Articles which contain favored tags

But what if I'm searching, and want to find articles with ALL of a set of tags?

For specificity, assume the following tables:

CREATE TABLE `articles` (
   `id` INT NOT NULL
);

CREATE TABLE `applied_tags` (
   `tag_id` INT NOT NULL,
   `article_id` INT NOT NULL
);

CREATE TABLE `search_tags` (
   `search_id` INT NOT NULL,
   `tag_id` TAG NOT NULL
);

I came up with this, which I think might work, but it's massive, ugly, and kinda unclear, so I figure there must be a better way:

Select articles.id from articles
where
 ( select count(*) from applied_tags
   where applied_tags.article_id == articles.id
     and applied_tags.tag_id in (select search_tags.tag_id from search_tags where search_tags.search_id == <input>) 
     ==
 (select count(*) from search_tags where search_tags.search_id == <input>)

(Essentially, count if the number of relevant tags is the expected value.)

Upvotes: 5

Views: 1198

Answers (1)

Harper Shelby
Harper Shelby

Reputation: 16583

select article_id
from  applied_tags 
where tag_id in (<input tag set here>)
group by article_id
having count(*) = <count of input tags>

This should do it. I won't swear it's the most efficient way, but it will do what you want, assuming that tag_id + article_id is the primary key on applied_tags. If it's not (i.e., you can have duplicate tags) all bets are off.

Upvotes: 8

Related Questions