MySQL select join where AND where

Question

I have two tables in my database:

Products

• id (int, primary key)
• name (varchar)

ProductTags

• product_id (int)
• tag_id (int)

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.

Vshaped

I dont understand what you are after? Do you care to elaborate with some data and examples? – Amorphism

Answer 1

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*/

Washcloth

This solves the problem. I wonder what number 3 stands for in the last line. I'm getting correct results with: HAVING COUNT(DISTINCT ProductTags.tag_id) = 1 and any size of tags id array: WHERE ProductTags.tag_id IN (1,2,3,4) – Vshaped

You would need to adjust the 3 as appropriate. For (1,2,3,4) you would need to use HAVING COUNT(DISTINCT ProductTags.tag_id) = 4 otherwise you would be bringing back those that had any 3 matching tags out of the 4. – Washcloth

Answer 2

you need to have a group by / count to ensure all are accounted for

select Products.*
  from Products 
         join ( SELECT Product_ID
                  FROM ProductTags
                  where ProductTags.tag_id IN (1,2,3)
                  GROUP BY Products.id
                  having count( distinct tag_id ) = 3 ) PreQuery
        on ON Products.id = PreQuery.product_id 

Swoop

Answer 3

The MySQL WHERE fieldname IN (1,2,3) is essentially shorthand for WHERE fieldname = 1 OR fieldname = 2 OR fieldname = 3. So if you aren't getting the desired functionality with WHERE ... IN then try switching to ORs. If that still doesn't give you the results you want, then perhaps WHERE ... IN is not the function you need to use.

Thunderbolt