SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) 
	Jul 25 2020 11:26:55 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE Employee (
  EmployeeId INTEGER,
  Name VARCHAR(4),
  DeptId INTEGER,
  IsDeleted INTEGER DEFAULT 0
);

INSERT INTO Employee
  (EmployeeId, Name, DeptId)
VALUES
  ('100', 'John', '100'),
  ('101', 'Kery', '100'),
  ('102', 'Tiny', '100');

CREATE TABLE Teams (
  TeamId INTEGER,
  TeamLeaderId INTEGER,
  TeamName VARCHAR(12),
  IsDeleted INTEGER DEFAULT 0
);

INSERT INTO Teams
  (TeamId, TeamLeaderId, TeamName)
VALUES
  ('1', '100', 'Finance-2021');

CREATE TABLE TeamMember (
  TeamMemberId INTEGER,
  TeamId INTEGER,
  EmployeeId INTEGER,
  IsDeleted INTEGER DEFAULT 0
);

INSERT INTO TeamMember
  (TeamMemberId, TeamId, EmployeeId)
VALUES
  ('1', '1', '101');
5 rows affected

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

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