Join two columns independently

Question

Suppose I have a table_1 with two columns: buyer_id and seller_id. I have another table, table_2, with two columns: person_id, city. My goal is a table with four columns: buyer_id, buyer_city, seller_id, seller_city.

I know I could get halfway there with something like

SELECT a.*, b.city as buyer_city FROM
table_1 a
JOIN table_2 b ON (a.buyer_id = b.person_id)

and then I could do a second query to get the seller. But is there any way to get what I want in a single query?

possible duplicate of [What's the best way to join on the same table twice?](http://stackoverflow.com/questions/4267929/whats-the-best-way-to-join-on-the-same-table-twice) — Frank Bryce, Jul 16 '15 at 17:24

score 2 · Answer 1 · answered Jul 16 '15 at 17:20

2

Join table_2 twice

SELECT a.*, 
       buyer.city as buyer_city, 
       seller.city as seller_city 
FROM table_1 a
JOIN table_2 buyer ON a.buyer_id = buyer.person_id
JOIN table_2 seller ON a.seller_id = seller.person_id

answered Jul 16 '15 at 17:20

juergen d

201,996
37
293
362

instead of to add a new answer...better to mark as Duplicate, until you have a far better answer – HaveNoDisplayName Jul 16 '15 at 17:59

Join two columns independently

1 Answers1