CODEWITHSUNDEEP
sql
Hrvoje T
Hrvoje T

Reputation: 3913

Select when two tables reference the same table

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

Upvotes: 1

Views: 199

Answers (1)

dugas
dugas

Reputation: 12453

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

Upvotes: 2

Related Questions