SQLTeam.com | Weblogs | Forums

Help joining 2 tables and selecting 1 field multiple times

Hi there,

Apologies, I am a beginner at SQL, and have 2 tables I want to join.
I want to display the same field (StaffName) in the results, twice, however they should be different names. I am not sure how to achieve this.

Table: Staff
StaffNo | StaffName
001 | John
002 | Jane
003 | Bob

Table: Task
Requested |Completed
001 | 003
002 | 003
003 | 001

Expected Result:
Requested | Completed
John | Bob
Jane | Bob
Bob | John

I can only join Staff.StaffNo to Task.Requested or Task.Completed. So when I select Staff.StaffName in my query, I can only get one result.

I hope this makes sense. Happy to answer any questions.
Thank you!

You need to use table aliases, which allow you to do different joins and to differentiate (tell the difference) between them:

SELECT sr.StaffName AS Requested, sc.StaffName AS Completed
FROM Task t
LEFT OUTER JOIN Staff sr ON sr.StaffNo = t.Requested
LEFT OUTER JOIN Staff sc ON sc.StaffNo = t.Completed

1 Like

Thank you Scott, I'll give this a go. :slight_smile: