CODEWITHSUNDEEP
mysqlmany-to-many
Majiy
Majiy

Reputation: 1920

mysql n:m relationship: Find rows with several specific relations

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?

Upvotes: 0

Views: 1509

Answers (2)

Zane Bien
Zane Bien

Reputation: 23125

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.

Upvotes: 5

Narendra
Narendra

Reputation: 3117

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.

Upvotes: 0

Related Questions