SQL连接两个表并检查两个表中每个值是否存在

Question

我们有三个主要的表Order,Pickup和Delivery一个OrderID下有多个item(1到多)

• 订单中的一些物品可能是提货而没有发货，反之亦然。
• 一些包含所有物品的订单可能在没有单个物品交付的情况下被提取，反之亦然。
• 一些订单的所有物品将被提取和交付。

所以我想要一个订单ID列表，然后是订单的提货项。如果取件时不存在订单id，但交付时存在订单id，则意味着取件丢失并显示"未取件"。反之亦然，如果物品在取件中，但没有在交付中，则意味着交付丢失，并将显示"未交付">

从上面的情况，您可以看到下面的示例数据与预期的结果

订单表

OrderID 
1
2
3
4
5

小表

OrderID | PickupItem | Date               OrderID | DeliveryItem | Date
1    |   1100     | 13-02-2021            1     |   1100       | 14-02-2021
2    |   2200     | 06-02-2021            2     |   2201       | 05-02-2021
2    |   2201     | 06-02-2021            3     |   3300       | 03-02-2021
3    |   3300     | 04-02-2021            3     |   3301       | 03-02-2021
4    |   4400     | 07-02-2021            5     |   5500       | 05-02-2021

预期的结果

OrderID | PickupItem       | PickupDate    | DeliveryItem      | DeliveryDate
1     |   1100           | 13-02-2021    |     1100          |    13-02-2021
2     |   2200           | 06-02-2021    |     Not Delivered |    Not Delivered
2     |   2201           | 06-02-2021    |     2201          |    13-02-2021
3     |   3300           | 04-02-2021    |     3300          |    13-02-2021
3     |   Not Picked Up  | Not Picked Up |     3301          |    13-02-2021
4     |   4400           | 07-02-2021    |     Not Delivered |    Not Delivered
5     |   Not Picked Up  | Not Picked Up |     5500          |    13-02-2021

Answer 1

您的数据库设计很差，因为您没有PickupItem存在的参考点。我们需要使用嵌套的FULL JOIN从两个表中获取结果:

select o.orderid, p.pickupitem, p.date,
d.deliveryitem, d.date
from [order] o
left join
(pickup p
full join delivery d
on d.DeliveryItem = p.pickupitem and d.orderid = p.orderid)
on o.orderid = isnull(d.orderid, p.orderid) ;

Answer 2

这是两个left joins，但它很棘手。第二个左连接需要引用两个表——一个用于订单id，另一个用于项目:

select o.orderid, p.pickupitem, p.date,
d.deliveryitem, d.date
from orders o left join
pickups p
on o.orderid = p.orderid left join
delivery d
on o.orderid = d.orderid and p.pickupitem = d.pickupitem;