sql - 选择一个具有两个左连接的条目。数据库

Question

例子如果两个产品

id  name
1   product A
2   product B

对于每个产品我都有属性

id product_id value
1  1           1
2  1           2
3  2           3
3  2           4

我需要根据属性值选择产品。我需要具有 1 和 2 个值的属性的产品。

此查询无效:

SELECT *
FROM product
LEFT JOIN attribute ON product.id = attribute.product_id
WHERE attribute.value = 1 AND attribute.value = 2;

Answer 1

进行分组以查找具有 1 和 2 属性的产品 ID。从该组找到产品 ID 的产品中选择:

SELECT *
FROM product_table
WHERE id IN (select product_id
             from attribute_table
             where value in (1,2)
             group by product_id
             having count(distinct value) = 2)

替代方案，双重连接:

SELECT *
FROM product_table
  JOIN attribute_table a1 ON product_table.id = a1.product_id
                          AND a1.value = 1
  JOIN attribute_table a2 ON product_table.id = a2.product_id
                          AND a2.value = 2