SQL Join using a Junction Table not returning expected results

I’m joining 3 tables in SQL Server for the purpose of mapping users to their managers. The purpose of the junction table is to map their full usernames to their network ID. Here’s the query:

select
    dbo.Employee_Records.EMPLOYEE_NAME as AD_NAME,
    dbo.Employee_Records.NAME as NAME,
    dbo.Employee_Records_MANAGER_S_.KEYWORD as SUPERVISOR_FULLNAME,
    dbo.employee_records.EMPLOYEE_NAME as SUPERVISOR_ADNAME
from
    dbo.Employee_Records
left outer join dbo.Employee_Records_MANAGER_S_ on
    dbo.Employee_Records.ID = dbo.Employee_Records_MANAGER_S_.ID
left join dbo.Employee_Records_MANAGER_S_ as ManagersList on
    ManagersList.KEYWORD = dbo.employee_records.name

The first 3 columns appear as I’d expect, showing the Network ID, Full User Name and the Manager’s Full Name. However, the 4th column is the issue. It’s showing the network ID of the user, not the manager.

It appears like this:

AD_NAME | NAME          | SUPERVISOR_FULLNAME | SUPERVISOR_ADNAME
USER1   | User, Test    | Supervisor, Test    | USER1
USER1   | User, Test    | Supervisor2, Test   | USER1

It should appear like this:

AD_NAME | NAME          | SUPERVISOR_FULLNAME | SUPERVISOR_ADNAME
USER1   | User, Test    | Supervisor, Test    | SUPERVISOR1
USER1   | User, Test    | Supervisor2, Test   | SUPERVISOR2

The Employee.Records table contains all full usernames and full supervisor names. The Employee.Records_MANAGER_S_ table is used to tie the Supervisors to the users, since each user could have multiple supervisors. All of the mappings for Network Names and Full Names are also in the Employee.Records table, so technically I’m trying to join the Employee.Records table to the Employee_Records_MANAGER_S_ and then do another join of the Employee_Recors_MANAGER_S_ back to the Employee.Records table again, but this time joining on the SUPERVISOR_FULLNAME instead of the Employee’s name.

Answer

If I understand correctly you need to join your dbo.Employee_Records table on a second time to get the supervisor details.

select
    ER.EMPLOYEE_NAME as AD_NAME,
    ER.[NAME] as [NAME],
    M.KEYWORD as SUPERVISOR_FULLNAME,
    MR.EMPLOYEE_NAME as SUPERVISOR_ADNAME
from dbo.Employee_Records as ER
left outer join dbo.Employee_Records_MANAGER_S_ as M on ER.ID = S.ID
left join dbo.Employee_Records as MR on MR.[NAME] = M.KEYWORD;

Note: As shown I highly recommend short table aliases as they make the query much clearer.

Advertisement

Answer