3

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'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?

Darrarski
  • 3,882
  • 6
  • 37
  • 59
  • The second query returns all products except those tagged with only 4, 5 or 6. If a product for example is tagged with 2 and 5, it will be included in the query. Thus combining the first and second query is pointless, as any product included by the first query would not be excluded by the second query. – Guffa Feb 16 '11 at 12:30

1 Answers1

4

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
Eero
  • 4,704
  • 4
  • 37
  • 40
  • Thanks a lot, it solves the problem. I assume that this cannot be achieved without using subquery, am I right? – Darrarski Feb 16 '11 at 13:12
  • Unfortunately, it's not exactly what I need. I don't know if using WHERE IN is a good idea. I would like to select for example products with tags 1 AND 2 AND 3, but not having tag 4 OR 5 OR 6. Above solution gives me products with tags 1 OR 2 OR 3 without tags 4 OR 5 OR 6. – Darrarski Feb 16 '11 at 13:50
  • Sure you can do that, although that is not how your question is worded. To include tags 1 AND 2, join on the ProductTags table twice: join ProductTags pt1 on p.id = pt1.product_id and pt1.tag_id = 1 join ProductTags pt2 on p.id = pt2.product_id and pt2.tag_id = 2 – Eero Feb 16 '11 at 16:06