Introduction
The very basics
Infinite looping
Processing trees with recursive CTEs
20. Hierarchical company 3/3
Processing graphs with recursive ctes
Summary

Instruction

Excellent! The last step is ahead of you.

Exercise

Now comes the hardest part. You are given the query from the previous exercise, but you need to transform it so that it goes deep down to every employee in the path. As a result, we want to see each employee in the company with their data and the path from the boss to that person.

Stuck? Here's a hint!

Create a new recursive CTE called Hierarchy. The content of the Boss CTE from the template should become the anchor member. Then, use UNION ALL and modify the external query from the template so that it uses recursion. Remember that the ID of the superior must match the ID that you got from the previous recursive step. Thanks to it – as the number of subordinates in our table is finite – the query will eventually stop. In other words, the termination check is not explicitly provided.

In the outer query, simply select all the information from your recursive CTE.

Don't forget to CAST the Unicode String Literal N'Boss' to NVARCHAR(MAX).