Getting null data for left join on same table twice

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?

Answer

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.