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 |
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.