By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Employees (
EmpID INT,
FirstName VARCHAR(35) NOT NULL,
LastName VARCHAR(35) NOT NULL,
ManagerID INT NOT NULL);
INSERT INTO Employees
(EmpID, FirstName, LastName, ManagerID)
VALUES
(1, 'John', 'Smith', 2),
(2, 'Anna', 'White', 1),
(3, 'Jack', 'Black', 4),
(4, 'Tim', 'Smith', 2),
(5, 'Jason', 'Black', 3),
(6, 'Tom', 'Black', 3);
WITH cte AS
(
SELECT EmpID,FirstName,LastName,ManagerID,1 level
FROM Employees
UNION ALL
SELECT t2.EmpID,t2.FirstName,t2.LastName,t2.ManagerID,level+ 1
FROM cte t1 JOIN Employees t2
on t1.EmpId = t2.ManagerID
WHERE t1.ManagerID <> t2.EmpId
)
SELECT EmpID,FirstName,LastName
FROM cte t1
where not exists (
select 1
from cte tt
WHERE tt.level = 2 and t1.EmpID = tt.EmpID
) and level = 1
| EmpID | FirstName | LastName |
|---|---|---|
| 1 | John | Smith |
| 2 | Anna | White |