mysql - 如何在一列中选择两个条件

Question

我有三个表:

餐 table 产品

ID | Name
1 | Pizza A
2 | Pizza B
3 | Pizza C

表格组件

ID | NAME
1 | cheese
2 | salami
3 | potato
4 | onion
5 | ananas

表product_components

ID | product_id | component_id
1 | 1 | 1
2 | 1 | 5
3 | 2 | 1
4 | 3 | 5
5 | 3 | 3

我只想买加奶酪和凤梨的披萨(披萨 A)。我怎样才能做到这一点？

Answer 1

select p.name
from product p
join product_components c1 on c1.id_pizza = p.id
join product_components c2 on c2.id_pizza = p.id
where c1.name = 'cheese'
  and c2.name = 'salami'

如果您想排除只含有奶酪和萨拉米香肠的比萨饼，您可以使用“排除 LEFT JOIN”来扩展查询:

select p.name
from product p
join product_components c1 on c1.id_pizza = p.id
join product_components c2 on c2.id_pizza = p.id
left join product_components other
  on  other.id_pizza = p.id
  and other.name not in (c1.name, c2.name)
where c1.name = 'cheese'
  and c2.name = 'salami'
  and other.name is null

更新

对于更改后的架构，查询将类似于

select p.name
from products p
join product_components pc1 on pc1.product_id = p.id
join components         c1  on c1.id = pc1.component_id
join product_components pc2 on pc2.product_id = p.id
join components         c2  on c2.id = pc2.component_id
where c1.name = 'cheese'
  and c2.name = 'salami'

select p.name
from products p
join product_components pc1 on pc1.product_id = p.id
join components         c1  on c1.id = pc1.component_id
join product_components pc2 on pc2.product_id = p.id
join components         c2  on c2.id = pc2.component_id
left join product_components pco
  on  pco.product_id = p.id
  and pco.component_id not in (pc1.component_id, pc2.component_id)
where c1.name = 'cheese'
  and c2.name = 'salami'
  and pco.component_id is null