mj sunny mj sunny - 1 year ago 62
MySQL Question

MySQL Join two table

I am making a query that is using user and reference table.

user table looks like this

user_id | user_name
1 | john
2 | smith
3 | sunny


and reference looks like this where reference_id is foreign key of user.user_id

id | user_id | reference_id
1 | 2 | 1
2 | 3 | 1


I can join these table and get below result

id | user_name | reference_id
1 | smith | 1


but what i want to do is instead of reference_id i want to select user_name of referenced user like this

quote_id | user_name | user_name(reference)
1 | smith | john

Answer Source

You'll have to join the user table in twice, then

 SELECT * FROM 
   reference r 
   INNER JOIN
   user u1 
   ON
     r.user_id = u1.user_id

   INNER JOIN
   user u2
   ON
     r.reference_id = u2.user_id

SQL fiddle: http://sqlfiddle.com/#!9/9469e/12/0

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download