Getting null data for left join on same table twice

user_7603419 0
Viewed 85

I have some tables like this:

Employee:

EmployeeId      Name        DeptId
----------------------------------
100             John        100
101             Kery        100
102             Tiny        100

Teams:

TeamId      TeamLeaderId    TeamName
------------------------------------------
1           100             Finance-2021

TeamMember:

TeamMemberId    TeamId      EmployeeId
----------------------------------------
1               1           101

I am trying to get list of employees with info regarding who they are : Team Member or Team Leader or Individual.

Current output:

TeamId      TeamName        EmployeeIdentity    EmployeeId    EmployeeName
------------------------------------------------------------------
null        null            Team Leader         100             John
1           Finance-2021    Team Member         101             Kery

Expected output:

TeamId      TeamName        EmployeeIdentity    EmployeeId      EmployeeName
-----------------------------------------------------------------------
1           Finance-2021    Team Leader         100             John
1           Finance-2021    Team Member         101             Kery

Query:

INSERT INTO #tempMembers (ID)
    (SELECT EmployeeId 
     FROM TeamMembers
     WHERE TeamId IN (1) AND IsDeleted = 0
     UNION   
     SELECT TeamLeaderId 
     FROM TEAMS
     WHERE TeamId IN (1))

SELECT
    t.TeamId as 'TeamId',
    t.TeamName as 'TeamName',
    CASE 
        WHEN tm.EmployeeId IS NOT NULL THEN 'Team Member'
        WHEN t1.TeamLeaderId IS NOT NULL THEN 'Team Leader'
        ELSE 'Individual'
    END AS 'EmployeeIdentity',
    e.EmployeeId as 'EmployeeId',
    e.EmployeeName as 'EmployeeName',
FROM
    Employee AS e
LEFT JOIN
    TeamMember tm ON tm.EmployeeId = e.EmployeeId AND tm.IsDeleted = 0
LEFT JOIN
    Teams t ON t.TeamId = tm.TeamId
LEFT JOIN
    Teams t1 ON t1.TeamLeaderId = e.EmployeeId
WHERE
    e.IsDeleted = 0 
    AND e.EmployeeId IN (SELECT * FROM #tempMembers)

Only issue here is that I am getting TeamId and TeamName as blank for "Team Leader" data as shown below:

Current output:

TeamId      TeamName        EmployeeIdentity    EmployeeId      EmployeeName
null        null            Team Leader         100             John

Can anyone please help me with this issue?

left-join sql-server sql
4 Answers

Here are one way:

select 
    t.teamid, t.teamName, 'Team Member' as EmployeeIdentity  ,    
    e.employeeid , e.emplyeename 
from
    Employee e 
join 
    TeamMember tm on tm.employeeid = e.employeeid 
join 
    teams t on tm.TeamId = t.TeamId
union all 
select 
    t.teamid, t.teamName, 'Team Leader' as EmployeeIdentity, 
    e.employeeid , e.emplyeename  
from 
    employees e 
join 
    Teams t on t.teamleaderid = e.employeeid

or even better:

select 
    t.teamid, t.teamName, EmployeeIdentity, e.employeeid, e.emplyeename 
from 
    Employee e 
join
    (select 
         TeamId, Employeeid, 'Team Member' as EmployeeIdentity 
     from TeamMember 
     union all 
     select teamid, TeamLeaderId, 'Team Leader' as EmployeeIdentity 
     from team) as tm on tm.employeeid = e.employeeid 
join 
    teams t on tm.TeamId = t.TeamId
user_13674540

I think your problem is that you are getting teamid and teamname from t, when in the leader case it comes from t1. Try this:

Case When t.TeamId is null then t1.TeamId else t.TeamId end as 'TeamId',
Case When t.TeamName is null then t1.TeamName else t.TeamId end as 'TeamName',
user_123211410

This approach does not need a temp table and uses the nulls from the left join to determine whether a member is a Team Member, Team Leader or Individual.

The where condition applies the filter from your initial union in the temp table.

I've also included a db-fiddle below to demonstrate.

SELECT
    CASE
       WHEN tl.TeamLeaderId IS NOT NULL THEN tl.TeamId
       WHEN tmt.TeamId IS NOT NULL THEN tmt.TeamId
       ELSE NULL
    END as TeamId,
    CASE
       WHEN tl.TeamLeaderId IS NOT NULL THEN tl.TeamName
       WHEN tmt.TeamName IS NOT NULL THEN tmt.TeamName
       ELSE NULL
    END as TeamName,
    CASE
        WHEN tl.TeamLeaderId IS NOT NULL THEN 'Team Leader'
        WHEN tm.EmployeeId IS NOT NULL THEN 'Team Member'
        ELSE 'Individual'
    END as EmployeeIdentity,
    e.EmployeeId,
    e.Name as EmployeeName
FROM
    Employee e
LEFT JOIN
    Teams tl ON e.EmployeeId = tl.TeamLeaderId AND tl.isDeleted=0
LEFT JOIN
    TeamMember tm ON e.EmployeeId = tm.EmployeeId AND tm.isDeleted=0
LEFT JOIN
    Teams tmt ON tm.TeamId = tmt.TeamId AND tmt.isDeleted=0
WHERE 
    tl.TeamId = 1 OR tmt.TeamId=1
TeamId TeamName EmployeeIdentity EmployeeId EmployeeName
1 Finance-2021 Team Leader 100 John
1 Finance-2021 Team Member 101 Kery

View on DB Fiddle or SQL Server Db Fiddle

Let me know if this works for you.

user_43071360

You can select both the team leader and the team members in an APPLY, then join Employee on that. No self-joins or temp tables are necessary

SELECT
  t.TeamId,
  t.TeamName,
  m.EmployeeIdentity,
  e.EmployeeId,
  e.Name AS EmployeeName
FROM Teams t
CROSS APPLY (
    SELECT
      t.TeamLeaderId AS EmployeeId,
      'Team Leader' AS EmployeeIdentity
    UNION ALL
    SELECT
      tm.EmployeeId,
      'Team Member'
    FROM TeamMember tm
    WHERE tm.TeamId = t.TeamId
) m
JOIN Employee e ON e.EmployeeId = m.EmployeeId
  AND e.IsDeleted = 0
WHERE t.TeamId = 1;

db<>fiddle.uk

user_148689970
Related