CODEWITHSUNDEEP
mysqlsql
user11452677
user11452677

Reputation: 3

select items with tags with ids 1 and 2, but not with the ids 3 or 4

I'm relating to the question Selecting an item matching multiple tags, where it was asked how to select items with tags with the ids 1 AND 2.

The given answer works great.

But what if I want all items with tags 1 AND 2, but not with tags 3 OR 4?

Is it still possible to do that with one query? I couldn't figure it out :-(

So this is my code to select video files with the tags with ids 155 AND 91 in the tags_map table:

SELECT DISTINCT
    vf.id,
    vf.title,
    vf.filename
FROM video_files vf
JOIN tags_map tm ON tm.video_file_id = vf.id
    AND tm.tag_id IN (155,91)
GROUP BY vf.id
HAVING COUNT(DISTINCT tm.tag_id) = 2 ORDER BY id ASC;

Is it possible to modify this one?

Sorry, yes, the question's title was unclear/wrong. I corrected it.

I'll try to make it clear:

item 1: has tags with ids 1 -> Not enough, I don't want to see it
item 2: has tags with ids 1 and 2 -> I do want to see it
item 3: has tags with ids 1, 2 and 3 -> It has tag with id 3 in it, I don't want to see it
item 4: has tags with ids 1, 2 and 4 -> It has tag with id 4 in it, I don't want to see it
item 5: has tags with ids 1, 2, 3 and 4 -> It has tags with id 3 and/or 4 in it, I don't want to see it

Upvotes: 0

Views: 89

Answers (4)

Obsidian
Obsidian

Reputation: 3897

This one should do the trick instead (and be compatible with MySQL) :

SELECT DISTINCT vf.id,
                vf.title,
                vf.filename

           FROM video_files vf

           JOIN tags_map ti  -- Tags In  (included)
             ON ti.video_file_id = vf.id
            AND ti.tag_id IN (1,2)

LEFT OUTER JOIN tags_map te  -- Tags Out (excluded)
             ON te.video_file_id = vf.id
            AND te.tag_id IN (3,4)

       GROUP BY vf.id,
                vf.title,
                vf.filename

         HAVING count(DISTINCT ti.tag_id) = 2
            AND count(DISTINCT te.tag_id) = 0

Upvotes: 0

Obsidian
Obsidian

Reputation: 3897

There's many way to do this but to my eyes, the best one remains EXCEPT for this situation:

SELECT DISTINCT
    vf.id,
    vf.title,
    vf.filename
FROM video_files vf
WHERE vf.id IN
(

    SELECT vf.id
      FROM video_files vf
      JOIN tags_map tm
        ON tm.video_file_id = vf.id
       AND tm.tag_id IN (1,2)
  GROUP BY vf.id
    HAVING count(tm.tag_id) >= 2

    EXCEPT

    SELECT vf.id
      FROM video_files vf
      JOIN tags_map tm
        ON tm.video_file_id = vf.id
       AND tm.tag_id IN (3,4)
)

The advantage here is that you can have an arbitrarily long tag list, in both cases (included and excluded ones). Just update the included tag count if you do (otherwise, we'd need to complicate the query to make sure that only the videos id that matches ALL the tags are selected).

Upvotes: 0

forpas
forpas

Reputation: 164099

For the case that you don't want 3 and 4, you only need to check the values of min and max tag:

select item
from itemtags
where tag in (1, 2, 3, 4)
group by item
having min(tag) = 1 and max(tag) = 2;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270011

You can use conditional aggregation:

select item
from itemtags
group by item
having sum(tag = 1) > 0 and
       sum(tag = 2) > 0 and
       sum(tag = 3) = 0 and
       sum(tag = 4) = 0;

Upvotes: 1

Related Questions