Sunday, January 7, 2018

SQL: Finding an assistant using a recursive CTE

Was looking for SQL that would allow me to find an Employee's assistant. It requires a somewhat recursive function:

create table EMPLOYEES
    (EmpID    int         unique Not null,
     Name    varchar(10),
     AssistantID int,
     Primary key(EmpID));

insert into EMPLOYEES values (1,'Bob', NULL);
insert into EMPLOYEES values (2,'Susan', 3);
insert into EMPLOYEES values (3,'John', 4);
insert into EMPLOYEES values (4,'Mary', 6);
insert into EMPLOYEES values (5,'Bill', NULL);
insert into EMPLOYEES values (6,'Fred', NULL);
insert into EMPLOYEES values (7,'Anderson', 1);
insert into EMPLOYEES values (8,'Gill', 9);
--insert into EMPLOYEES values (9,'Jerry', 8);

I wanted to for example find Mary's assistant (e:g Fred). There's a recursive case where to find Susan's assistant we go: Susan -> John -> Mary -> Fred. I also want Bob's assistant which is himself.
Here's one solution:

WITH RCTE AS
(
    SELECT * , EmpID AS TopEmp
    FROM EMPLOYEES c
 
    UNION ALL
    SELECT c.* , r.TopEmp
    FROM dbo.EMPLOYEES c
     INNER JOIN RCTE r ON c.EmpID = r.AssistantID
)
select EmployeeName,Assistant from
(
SELECT
  e.EmpID AS EmployeeId,
  e.Name AS EmployeeName,
  r.Name AS Assistant,
  r.EmpID AS AssistantId
FROM RCTE r
inner join EMPLOYEES e ON r.TopEmp = e.EmpID
where r.AssistantID is null
) Records

Result:

| EmployeeName | Assistant |
|--------------|-----------|
|          Bob |       Bob |
|         Bill |      Bill |
|         Fred |      Fred |
|     Anderson |       Bob |
|         Mary |      Fred |
|         John |      Fred |
|        Susan |      Fred |