Jack Garay Jack Garay - 1 year ago 44
SQL Question

SQL Tables of the Same Column to Join

I currently have a problem as how to fetch data separately, in the same table, but of different conditions.

To better illustrate, take the example below as my tables.

disputes table

id | user_to | bidder_id
1 | 1 | 2

users table

user_id | user_name
1 | lawrencetecho
2 | joshuaisanan

I'd like to have an output that combines both like this:

final output table

id | user_to | bidder_id | user_to_name | bidder_id_name
1 | 1 | 2 | lawrencetecho | joshuaisanan

I do not know how to really put it into words but I hope the illustration helps :

It seeks for the "user_to" and "bidder_id" rows, associates them to the "user_id" in the users table, where it creates two new columns that associates the "user_id" and "bidder_id" to the respective ids in the users table and fetches the user_name in the id given in the field.

Answer Source

LEFT JOIN is your friend. see the exsample:


SELECT d.*, 
 utn.user_name AS user_to_name , 
 bin.user_name AS bidder_id_name
FROM disputes d
LEFT JOIN users utn on utn.user_id = d.user_to
LEFT JOIN users bin on bin.user_id = d.bidder_id;