Help joining 2 tables and selecting 1 field multiple times

Transact-SQL
1

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!

2

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
3

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