MilkACow MilkACow - 7 months ago 13
SQL Question

MYSQL Join Multiple times on same table

I am trying to pull both owner and editby. Both of those fields are INT. Inside a simple table, for example:

users:

user_id user_name
-----------------
2 johnny
3 mecca


doc:

owner content editby
----------------------
2 misc 3


SQL:

SELECT doc.owner, doc.content, doc.editby, users.user_name
FROM doc
LEFT JOIN
users
ON
users.user_id = doc.owner
WHERE
doc_id = $id


I can grab owner user_name, but I am not sure how to obtain editby on the same table. How do I go about pulling the different user names for different id fields multiple times?

Answer

Join the users table twice with different aliases

SELECT doc.owner, doc.content, 
       e.user_name as editor, 
       o.user_name as owner 
FROM doc
LEFT JOIN users o ON o.user_id = doc.owner
LEFT JOIN users e ON e.user_id = doc.editby
WHERE doc_id = $id
Comments