Jack Garay Jack Garay - 6 months ago 9
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

LEFT JOIN is your friend. see the exsample:

sample

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;
Comments