使用多个标签搜索标签表或在MySQL中选择“全部除外”

Question

Is there a way to select all except those who match?

I am working with a tag table (article_id, tag) and an article table (article_id, title, ...);

My current problem: The existing search worked like this:

SELECT DISTINCT `article_id` FROM `TagTable` WHERE `tag` IN ('tag1', 'tag2')

Unfortunately this way the user gets the results that contain either one of the tags while you would only want articles that match both tags.

How would you implement it? Several subqueries for each tag seems a dumb idea. My idea would be to select all and remove everyone that dose not have each of the tags. Any better Ideas?

Thank you in advance.

Answer 1

hmm.

Well the easiest way is to use a subquery:

SELECT a.*
FROM articles a
WHERE a.id IN (
    SELECT article_id FROM TagTable WHERE tag = 'tag1'
) AND a.id IN (
    SELECT article_id FROM TagTable WHERE tag = 'tag2'
);

But since you said you don't want to do that, you could try something like:

SELECT article_id, count(DISTINCT tag)
FROM TagTable
WHERE tag IN ('tag1', 'tag2')
GROUP BY article_id
HAVING count(DISTINCT tag) = 2

I haven't tested it yet, but something like that should work. you just have to make sure the HAVING clause equals the number of tags you are looking for.