CODEWITHSUNDEEP
mysqlselectjoinwhere-in
Darrarski
Darrarski

Reputation: 4032

MySQL select where in but not in with join

I have three tables in my database:

Products

Tags

ProductTags

I'm doing SQL query to select products with assigned tags with given id's:

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 given id's:

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 that queries to select products having given tags, but not having other tags? I've tried to achieve this that 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's not working obviously, giving me products with tags (1,2,3), no matter they has tags (4,5,6) assigned or not. Is this possible to solve this problem using one query?

Upvotes: 3

Views: 2547

Answers (1)

Eero
Eero

Reputation: 4754

Use a subquery to filter out the list of products that contain unwanted tags:

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

Upvotes: 4

Related Questions