MySQL chooses where, but not internally, with a join

Question

I have three tables in my database:

Products

• id (int, primary key)
• name (varchar)

Tags

• id (int, primary key)
• name (varchar)

Producttags

• product_id (int)
• tag_id (int)

I am making an SQL query to select products with assigned tags with the given identifiers:

SELECT * FROM Products
JOIN ProductTags ON Products.id = ProductTags.product_id
WHERE ProductTags.tag_id IN (1,2,3)
GROUP BY Products.id

I can either select products without assigned tags with the given identifiers:

SELECT * FROM Products
JOIN ProductTags ON Products.id = ProductTags.product_id
WHERE ProductTags.tag_id NOT IN (4,5,6)
GROUP BY Products.id

How could I combine these queries to select products that have tags but no other tags? I tried to achieve this in this way:

SELECT * FROM Products
JOIN ProductTags ON Products.id = ProductTags.product_id
WHERE ProductTags.tag_id IN (1,2,3)
AND ProductTags.tag_id NOT IN (4,5,6)
GROUP BY Products.id

But it doesn't work explicitly, giving me products with tags (1,2,3), regardless of whether they have tags (4,5,6) assigned or not. Is it possible to solve this problem with a single request?

Answer 1

, , :

SELECT * FROM Products
  JOIN ProductTags ON Products.id = ProductTags.product_id
  WHERE ProductTags.tag_id IN (1,2,3)
    AND Products.id NOT IN (SELECT product_id FROM ProductTags WHERE tag_id IN (4,5,6))
  GROUP BY Products.id