如何编写SQL实现多表JOIN查询同时满足蓝白双色且品牌为my_brand_1的产品

正确查询SQL #

写法1（分组统计法，推荐）

SELECT p.prodoct_id
FROM prodocts p
INNER JOIN brands b 
  ON p.prodoct_id = b.prodoct_id 
  AND b.name = 'my_brand_1'
INNER JOIN colors c 
  ON p.prodoct_id = c.prodoct_id 
  AND c.name IN ('blue', 'white')
GROUP BY p.prodoct_id
HAVING COUNT(DISTINCT c.name) = 2;

写法2（多表关联法）

如果不想用分组逻辑，也可以分别关联两次颜色表匹配两个颜色条件：

SELECT p.prodoct_id
FROM prodocts p
INNER JOIN brands b 
  ON p.prodoct_id = b.prodoct_id 
  AND b.name = 'my_brand_1'
INNER JOIN colors c_blue
  ON p.prodoct_id = c_blue.prodoct_id 
  AND c_blue.name = 'blue'
INNER JOIN colors c_white
  ON p.prodoct_id = c_white.prodoct_id 
  AND c_white.name = 'white';

原SQL错误说明 #

你的原写法核心问题是写了colors.name = 'blue' AND colors.name = 'white'的关联条件，单条颜色记录的name字段不可能同时等于两个不同的值，这个条件永远不成立，所以颜色表永远匹配不到数据。同时你使用了LEFT OUTER JOIN，即使关联失败也会保留所有产品记录，完全达不到过滤效果。

逻辑说明 #

1. 用INNER JOIN关联品牌表，直接过滤出品牌为my_brand_1的产品
2. 两种写法分别通过分组统计双颜色匹配、分别关联两种颜色的方式，确保返回的产品同时拥有蓝色、白色两个属性

内容的提问来源于stack exchange，提问作者dasdasd