WITH EmpsCTE AS ( SELECT empid, mgrid, firstname, lastname, 0 AS distance FROM HR.Employees WHERE empid = 9 UNION ALL SELECT M.empid, M.mgrid, M.firstname, M.lastname, S.distance + 1 AS distance FROM EmpsCTE AS S JOIN HR.Employees AS M ON S.mgrid = M.empid ) SELECT empid, mgrid, firstname, lastname, distance FROM EmpsCTE;
- 4. Combining Sets
- from Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012
- Publisher: Microsoft Press
- Released: December 2012
This query is recursive. Because the recursive (or subsequent query) makes reference to the anchor query, the recursive query will repeat until the set is empty. Because the recursive query joins mgrid to empid, each recurrence moves the manager's ID to the employeee id in the next iteration.
Share this highlighthttp://www.safaribooksonline.com/a/training-kit-exam/59564/