CODEWITHSUNDEEP
mysqlsqldatabasejoinselect
Ab Le
Ab Le

Reputation: 65

Multiple select user name SQL

Hi I have 3 table that I want to join

Table Export

id | id_product | id_user  | id_loan
1  |     516    |    10    |    1

Table User

id |    Name    |
 5 |    John    |
10 |    Justin  |

Table Loan

id |   id_user  | Quantity |
 1 |     5      |    23    |

My sql code is

Select *
From Export
join User ON Export.id_user = User.id
join Loan ON Export.id_loan = Loan.id

How can I select that my result will be

ID Product | Export User | Loan User | Quantity
   516     |    Justin   |   John    |    23

Thanks you

Upvotes: 0

Views: 88

Answers (4)

Martin
Martin

Reputation: 16433

You need to JOIN to the tables appropriately to get the result you want:

SELECT  e.id_product AS `ID Product`,
        ue.Name AS `Export User`,
        ul.Name AS `Loan User`,
        l.quantity
  FROM  Export e
    LEFT JOIN User ue ON ue.id = e.id_user
    LEFT JOIN Loan l ON l.id = e.id_loan
    LEFT JOIN User ul ON ul.id = l.id_user

The first JOIN to the User table gets the export user (as ue) and the second gets the loan user (as ul).

Output:

ID Product  Export User   Loan User     quantity
516         Justin        John          23

A working fiddle showing this in action is here.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

You need the table use two time with proper table name alias 

  Select Export.id_product, a.name esport_user, b.name loan_user, Loan.quantity 
  From Export
  join User a ON Export.id_user = User.id
  join User b ON Export.id_loan = User.id
  join Loan ON Export.id_loan = Loan.id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You just need one more join:

Select e.id_product, u.name as export_user, ul.name as loan_user, l.quantity
From Export e join
     User u
     on e.id_user = u.id join
     Loan l
     on e.id_loan = l.id join
     User ul
     on l.id_user = ul.id;

Note that if either of the users could be null, you probably want left joins:

Select e.id_product, u.name as export_user, ul.name as loan_user, l.quantity
From Export e left join
     User u
     on e.id_user = u.id left join
     Loan l
     on e.id_loan = l.id left join
     User ul
     on l.id_user = ul.id;

This will keep all rows in export, even when there are not matches in the other tables.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You need to join with multiple instance of user

Select id_product,User.name as exportUser,u1.name as loanUser,quantity
From Export
left join User ON Export.id_user = User.id
left join Loan ON Export.id_loan = Loan.id
left join user u1 on Export.id_loan=u1.id

Upvotes: 1

Related Questions