Cascading SQL select

Question

I have a table of employees. Each employee row contains the employee's manager. One manager will have one or more employees and an employee may also be a manager.

I'm trying to construct a query that will return ALL the employees of a given manager.

For example, Manager A has Employees B,C, and D. Employee B is a manger of E,F, and G. Employee C is a manager of H and I. D has no direct reports. E has Y and Z as direct reports.

So, if I queried on A, I would expect to get B,C,D,E,F,G,H,Y, and Z as a result. If I queried on B, I should get E,F,G,Y and Z.

Basically, the query needs to continue its cascade until all employees with any connection whatsoever to the selected manager are returned.

I can get one level of iteration via:

select fullname from employees where manager = 'XXX' 
or manager in (select fullname from employees where manager='XXXX')

Answer 1

Recursive CTE will help you:

DECLARE @m nvarchar(1) = 'B'
;WITH Employee AS (
SELECT *
FROM (VALUES
('A',NULL),
('B','A'),
('C','A'),
('D','A'),
('E','B'),
('F','B'),
('G','B'),
('H','C'),
('I','C'),
('Y','E'),
('Z','E')
) as t(Employee, Manager)
), cte AS (
SELECT *
FROM Employee
WHERE Manager = @m
UNION ALL
SELECT e.*
FROM cte c
INNER JOIN Employee e
ON e.Manager = c.Employee
)

SELECT *
FROM cte

Output for B:

Employee Manager
-------- -------
E        B
F        B
G        B
Y        E
Z        E

Output for A:

Employee Manager
-------- -------
B        A
C        A
D        A
H        C
I        C
E        B
F        B
G        B
Y        E
Z        E