0

I have two SQL Tables, 'products' and 'tags'. They have an n:m relationship, using a third table 'product_tags'.

I want to use a query to find every product that has a number of specific tags. For example, find every products that has a relation to the tags 1, 23 and 54.

Is there a way to do this with just one query?

Majiy
  • 1,890
  • 2
  • 24
  • 32

2 Answers2

5

You can use this solution. This gets all products that contain ALL keywords 1, 23, and 54:

SELECT a.*
FROM products a
INNER JOIN product_tags b ON a.product_id = b.product_id
WHERE b.tag_id IN (1,23,54)
GROUP BY a.product_id
HAVING COUNT(1) = 3

Where 3 is the number of items in your WHERE IN list, so you can adjust accordingly based on the amount of tags you want to check on.

Zane Bien
  • 22,685
  • 6
  • 45
  • 57
  • Please tell the need of COUNT(1) = 3 – Narendra Jul 10 '12 at 09:14
  • It works perfectly, if i replace the HAVING clause with the following: `HAVING COUNT(b.tag_id) = 3` and add a `GROUP BY a.id` – Majiy Jul 10 '12 at 09:14
  • The reason behind the COUNT is, that WHERE IN(...) doesn´t care how many of the elements inside the brackets match. Without the COUNT, the result would be "every product that has any of the tags" instead of "every product that has exactly the tags". – Majiy Jul 10 '12 at 09:25
  • just one question: how will this perform on a million row table (product_tags), with very frequent reads? and occasional adds (100s in 24 hrs - no burst), modify/delete (10s in 24 hrs). nominal hardware (4gb ram, core i3) – rajeev Sep 01 '16 at 16:05
  • how do you have this in equivalent DBIx syntax? – rajeev Sep 02 '16 at 01:04
0

Try this

SELECT * FROM

 PRODUCTS p 
 JOIN PRODUCT_TAGS pt ON p.ID = pt.PRODUCT_ID
 JOIN TAGS t ON pt.TAG_ID = t.ID AND
      t.ID IN (1, 23, 54)

I am assuming the column names. I think the column names are straight forward and you can use your column names easily. if you need more clarification let me know.

Narendra
  • 3,069
  • 7
  • 30
  • 51