sql - 未找到匹配项时左连接结果为空

Question

假设我有这三个表:

Table: Baskets
id  |  name
1      Sale
2      Premium
3      Standard
4      Expired

Table: Fruit
id  |  name   | basketid
1      Apples    1
2      Oranges   2
3      Grapes    3
4      Apples    2
5      Apples    4

Table: Veggies
id  |  name   | basketid
1      Carrots     1
2      Peas        2
3      Asparagus   1

看起来后两个表应该只是一个名为 produce 的表，但在实际情况中，它们是不同的表是有充分理由的。如果篮子在水果表或蔬菜表中有行，我需要编写一个返回行的连接。我想我可以用两个左连接来完成这个:

Select Baskets.*, fruit.name as fruit,
veggies.name as veggies 
from Baskets
left join Fruit on Baskets.id =  Fruit.basketid 
left join veggies on Baskets.id = Veggies.basketid 
where Baskets.id = 2;

但是此语句返回我希望为空的字段中的值。实际输出:

id  | name     |  fruit   |  veggies
2     Premium    Oranges     Peas
2     Premium    Apples      Peas

我想要的输出:

id  | name     |  fruit   |  veggies
2     Premium    Oranges     
2     Premium    Apples      
2     Premium               Peas

我怎样才能做到这一点？

Answer 1

您需要将 Fruits 和 Vegetables 表合并为一个您加入的关系，a la:

Select Baskets.*, produce.fruitname as fruit, produce.veggiename as veggies 
from Baskets
left join (SELECT basketid, name as fruitname, NULL as veggiename
           FROM fruit
           UNION
           SELECT basketid, NULL, name
           FROM veggies) produce
      ON baskets.id = produce.basketid
where Baskets.id = 2;