MySQL select join where AND where
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5017680/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL select join where AND where
提问by Darrarski
I have two tables in my database:
我的数据库中有两个表：
Products
产品
- id (int, primary key)
- name (varchar)
- id（整数，主键）
- 名称 (varchar)
ProductTags
产品标签
- product_id (int)
- tag_id (int)
- 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 = 2is pointless, because no rows will be returned.
但它为我提供了具有任何给定标签的产品，而不是所有给定的标签。写入WHERE tag_id = 1 AND tag_id = 2毫无意义，因为不会返回任何行。
回答by Martin Smith
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*/
回答by DRapp
you need to have a group by / count to ensure all are accounted for
你需要有一个 group by / count 来确保所有的都被考虑在内
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
回答by WNRosenberg
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 ... INthen try switching to ORs. If that still doesn't give you the results you want, then perhaps WHERE ... INis not the function you need to use.
MySQLWHERE fieldname IN (1,2,3)本质上是WHERE fieldname = 1 OR fieldname = 2 OR fieldname = 3. 因此，如果您没有获得所需的功能，请WHERE ... IN尝试切换到ORs。如果这仍然没有给您想要的结果，那么可能WHERE ... IN不是您需要使用的功能。
