select both ways from mysql

Question

i need to select all transactions for a particular user from a loans table in mysql i have two tables loans and users

loans

id borrower_id lender_id amnt
1  1           2         10
2  1           3         5
3  2           1         2

for users

id name
1  bill
2  gates
3  microsoft

so if i select for bill using his id 1, i should get this expected output

output

id uid name      borrower_id lender_id amnt
1  2   gates     1               2         
2  3   microsoft 1               3
3  2   gates     2               1

as you can see in the loans table, a user can both borrow or lend loans this is what i tried

SELECT loans.*, users.name, users.id as uid 
From loans
   Left Join users On users.id = loans.lender_id 
   Left Join users On users.id = loans.borrower_id
Where
  (loans.lender_id = 2 ) Or (loans.borrower_id = 2 );

but am getting syntax errors

#1066 - Not unique table/alias: 'users'

Pls i made and edit in the output

what am i doing wrong?

Answer 1

When you use same table to write JOIN query, then you must be assign different alias to it. So that here you have to use different alias for users table. If same alias is there then its difficult for MySQL to differentiate both alias of user table. So you have to manually assign different alias.

You have to check for each table that there should not be NULL as you are using same table twice.

SELECT 
   loans.*, 
   IF(ul.name = NULL, ub.name, ul.name) as name, 
   IF(ul.id = NULL, ub.id,ul.id) as uid 
FROM
   loans
LEFT JOIN 
   users ul ON users.id = loans.lender_id 
LEFT JOIN 
   users ub ON users.id = loans.borrower_id
WHERE 
   ul.id = 2 OR ub.id = 2;