join same table twice?? Can it be done?

[nikko50]

I have a table called users that have 2 fields that hold a userid for the Assigned_To and userid for the Created_By field. Can I join the table twice to get the full name field like below?

SELECT repairs,*,users.fullname,users.fullname AS Assigned_To
from repairs

LEFT JOIN users ON repairs.Created_By = users.userid
LEFT JOIN users ON repairs.Assigned_To= users.userid

[laserlight]

You should be able to use aliases, e.g.,

SELECT repairs.*,creators.fullname,assignees.fullname AS Assigned_To
from repairs
LEFT JOIN users creators ON repairs.Created_By = creators.userid
LEFT JOIN users assignees ON repairs.Assigned_To = assignees.userid

[nikko50]

Worked like a charm!

[laserlight]

Good to hear 🙂
Remember to mark this thread as resolved (if it is) using the thread tools.