MySQL选择连接在何处AND

I have two tables in my database:

我的数据库中有两个表:

Products

id (int, primary key)

id(int,主键)

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.

但它给了我带有任何给定标签的产品,而不是所有给定的标签。编写WHERE tag_id = 1 AND tag_id = 2是没有意义的,因为不会返回任何行。

3 个解决方案

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

#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

#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.

MySQL WHERE字段名IN(1,2,3)本质上是WHERE fieldname = 1或fieldname = 2 OR fieldname = 3的简写。因此,如果您没有使用WHERE ... IN获得所需的功能,请尝试切换到OR 。如果仍然没有给你想要的结果,那么也许WHERE ... IN不是你需要使用的功能。

#1