sql - 两个表引用同一个表时选择

Question

我有两个表，tSellers 和 tBuyers。他们都有对表 tCity 的 FK。如何在订单表中从买家和卖家中加入和选择城市名称？

tOrder     tSeller   tBuyer   tCity
orderID    sellerID  buyerID  cityID
sellerID   cityID    cityID   name
buyerID

select o.orderID, c.name as buyerCity, c.name as sellerCity
from tOrder o
join tSeller s on s.sellerID = o.sellerID
join tBuyer b on b.buyerID = o.buyerID
join tCity c on c.cityID = ??

预期输出:

orderID  buyerCity sellerCity
      1  London    Paris

Answer 1

扩展@jarlh 的评论:
两次加入 City 表的示例，一次是卖方(获得卖方的城市)，一次是买方(获得买方的城市)。

select o.orderID, buyerCity.name as BuyerCity, sellerCity.name as SellerCity
from tOrder o
join tSeller s on s.sellerID = o.sellerID
join tBuyer b on b.buyerID = o.buyerID
join tCity sellerCity on s.cityID =  sellerCity.cityID
join tCity buyerCity on b.cityID = buyerCity.cityID