MySQL select join where AND where

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 开发者_Python百科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*/

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 

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.