I have two tables in my database:
Products
ProductTags
I would like to select products having all given tags. I tried:
SELECT
*
FROM
Products
JOIN ProductTags ON Products.id = ProductTags.product_id
WHERE
ProductTags.tag_id IN (1, 2, 3)
GROUP BY
Products.id
But it gives me products having any of given tags, instead of having all given tags. Writing WHERE tag_id = 1 AND tag_id = 2
is pointless, because no rows will be returned.
This type of problem is known as relational division
SELECT Products.*
FROM Products
JOIN ProductTags ON Products.id = ProductTags.product_id
WHERE ProductTags.tag_id IN (1,2,3)
GROUP BY Products.id /*<--This is OK in MySQL other RDBMSs
would want the whole SELECT list*/
HAVING COUNT(DISTINCT ProductTags.tag_id) = 3 /*Assuming that there is a unique
constraint on product_id,tag_id you
don't need the DISTINCT*/
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With