Search code examples
sqlssms-16

Join the same descriptor variable for 2 different variables

I'm sure this is very basic, but I'm having a hard time and am not having luck searching for similar questions that have already been answered. I'm joining up tables and need to join and return the same variable for two different variables. That's probably not a great explanation - here's an example:

Table1
RequestingStaffMemberID
DocumentNo

Table2
CollectingStaffMemberID
DocumentNo

Table3
StaffMemberID
StaffMemberName

I want to join up table 1&3 and table 2&3 on StaffMemberID so that I can return:
StaffMemberName(as StaffMemberID=RequestingStaffMemberID)
and StaffMemberName(as StaffMemberID=CollectingStaffMemberID)

How do I cast/convert/declare something to do this so that I'm not asking it to return StaffMemberName twice? Right now I just have it return the CollectingStaffMemberID and index/match it offline in Excel because I was in a pinch, but I want to learn how to do this correctly in SQL.

SELECT a.DocumentNo, c.StaffMemberName, b.CollectingStaffMemberID

FROM Table1 as a

left join Table2 as b on
a.DocumentNo=b.DocumentNo

left join Table3 as c on 
a.RequestingStaffMemberID=c.StaffMemberID

GROUP BY a.DocumentNo, c.StaffMemberName, b.CollectingStaffMemberID

Thank you for any help!

Solution

  • I guess you're looking for something like this:

    SELECT  Requests.DocumentNo
,       Staff_Req.StaffMemberName AS RequestStaffMemberName
,       Staff_Col.StaffMemberName AS CollectionStaffMemberName
FROM    Table1 as Requests
LEFT JOIN Table2 as Collections 
        ON Requests.DocumentNo = Collections.DocumentNo
LEFT JOIN Table3 as Staff_Req
        ON Staff_Req.StaffMemberID = Requests.RequestingStaffMemberID
LEFT JOIN Table3 as Staff_Col
        ON Staff_Col.StaffMemberID = Collections.CollectingStaffMemberID

    I also gave your tables better aliases, so you know what's what (although I'm sure your actual tables have more descriptive names).