I have two tables, tSellers and tBuyers. They both have FK to a table tCity. How do I join and select city name from a buyer and a seller in a order table?
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 = ??
Expected output:
orderID buyerCity sellerCity 1 London Paris
Advertisement
Answer
To expand on @jarlh ‘s comment:
Example of joining on City table twice, once for Seller (to get the city of the Seller), and once for Buyer (to get the city of the Buyer).
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