mysql - 通过连接表满足多个 WHERE 条件

Question

我有一张桌子，products和另一个，categories。两者由 关联products_categories。一个产品可以出现在多个类别中。

我正在尝试做的是非冗余返回满足所有条件的结果。例子：

 SELECT *
  FROM products as p
  JOIN products_categories AS pc 
    ON p.PRODUCTID = pc.productid
  JOIN categories AS c 
    ON c.id = pc.category
 WHERE c.category = 'All Products' 
   AND c.category = 'On Sale' 
   AND c.category = 'Active'
 GROUP 
    BY p.PRODUCTID

显然，我在某个地方出错了。在这种情况下，我想返回 、 和 类别中的All Products任何On Sale内容Active。

标签： mysqljoin

Answer 1

将子句中的所有ANDs 更改为ORs，WHERE如果您想要属于所有 3 个类别的产品，请添加一个HAVING子句：

SELECT p.PRODUCTID
FROM products as p
JOIN products_categories AS pc ON p.PRODUCTID = pc.productid
JOIN categories AS c ON c.id = pc.category
WHERE c.category = 'All Products' OR c.category = 'On Sale' OR c.category = 'Active'
GROUP BY p.PRODUCTID
HAVING COUNT(c.id) = 3