Practice What You've Learned

To practice using common table expressions (CTEs) to manipulate trees, try to devise solutions to the following three requests and compare them to the solutions I provide in Listings A, B, and C. All solutions should apply to the Employee table in the Adventure Works database. For all the requests, try to write a generic solution. For example, if you need to return all direct and indirect subordinates of Employee 12, you could declare a variable called @EmpID, set its value to 12, and use the variable in the CTE instead of using the literal 12.

Main article: Get in the Loop with CTEs

Here are the three requests:

1. Return the management chain leading to Employee 217. As Listing A shows, the Anchor Member returns the row for the given employee. By referring to the CTE's name, the Recursive Member returns the manager of the employee that the previous step returned. Thus, each invocation returns the next level's manager.
2. Return the manager of Employee 26, two levels up (i.e., the manager of Employee 26's manager). As Listing B shows, the Anchor Member returns the row for the given employee. By referring to the CTE's name, the Recursive Member returns the manager of the employee the previous step returned. Again, each invocation returns the next level's manager. A pseudo column limits the number of levels to the given value in the @Levels variable—in this case, 2.
3. Thanks to Lubor Kollar, a group program manager on Microsoft's SQL Server engine team, for sending me the following beautiful puzzle. Run the code that Listing D shows to generate a table called Forest, and use it as test data for this problem. The Forest table contains three hierarchies of employees. Given Employee 301, return all leaf employees (employees who aren't managers) in the same employee tree. The solution in Listing C uses two CTEs, each implementing techniques from the main article. The first CTE, EmpPathCTE, returns the management chain leading to the given employee (including the top manager whose MgrID=NULL). The second CTE, EmpTreeCTE, returns the whole tree starting with the top manager that EmpPathCTE returned. The outer query's filter contains a NOT EXISTS predicate that returns only non-manager employees from the tree of employees that EmpTreeCTE returned.