T‑SQL recursion is a bit like Yeti. Everyone knows it does exist but when you have to use it, you usually don’t know how to do it. I admit that I had a problem with it myself and hence the idea for this post. In my opinion, the cause of that problem was not only in my lack of a full understanding of the topic but also the fact that usually the recursion materials in T‑SQL focus exclusively on the use of CTE (Common Table Expression) without taking a closer look at the problem being solved. For a change, I would like to approach this topic a bit differently and present two specific issues related to reconstructing the hierarchy using recursion and show how to deal with them.

For considerations, let me introduce a small company whose president is Mr. Mateusz Orzechowski. The company has two departments with Mrs. Karolina Malinowska and Mr. Julian Kubicki as managers. Mrs. Amelia Przybylska and Mrs. Maja Wesołowska, work in Mrs. Karolina’s department, while Messrs. Jakub Witkowski, Karol Tomaszewski and Krzysztof Jankowski work in Mr. Julian’s department. Of course, the names and surnames are made up and any resemblance to real people is completely coincidental 🙂

The above organizational structure can be created using the following code and it will be the starting point for our further activities.

 declare @workers as table (
     idWorker int,
     idManager int,
     name nvarchar(100)
 )
 insert into @workers values
 (1, null, N'Mateusz Orzechowski'),
 (2, 1, N'Karolina Malinowska'),
 (3, 1, N'Julian Kubicki'),
 (4, 2, N'Amelia Przybylska'),
 (5, 2, N'Maja Wesołowska'),
 (6, 3, N'Jakub Witkowski'),
 (7, 3, N'Karol Tomaszewski'),
 (8, 3, N'Krzysztof Jankowski');
 

 It is easy to see that the president does not have a supervisor, and therefore in his case the field with the manager’s ID is empty.

The two most natural questions about hierarchy that any employee can ask themselves are:

1. who is below me in the hierarchy,
2. who is above me in the hierarchy.

I will call the first case the top-down approach because we will start from a person higher in the hierarchy and move downwards. I will analogically name the second case the bottom-up approach, because the starting point will be a person lower in the hierarchy and we will move up (up to the highest person in the hierarchy – in our case, Mr. Mateusz Orzechowski who is the president).

Before we go any further, let’s take a moment to the theory. Each recursive query in T‑SQL looks similar – the two most important elements appearing in each such query are WITH which defines CTE, and UNION ALL which allows you to combine successive recursion results into one result table. An example query in the hierarchy problem we are considering may look like this:

 declare @person int = 3;
 with hierarchy as (
     select *, 0 as level     
     from @workers
     where idWorker = @person
     union all
     select p.*, (level + 1) as level
     from @workers as p
     join hierarchy as h on p.idManager = h.idWorker
 )
 select *
 from hierarchy;
 

As you can see, the query in the CTE part consists of two elements. The first is a query for the first element (i.e. the one from which we start building the hierarchy). In the above example, we start building the tree with the employee with ID 3, i.e. from Mr. Julian Kubicki. The other part is the second query connected to the first one by the UNION ALL operator – in this query we retrieve the data of an worker who is in an appropriate relationship with previously found employees, which was expressed by JOIN clause. In this particular case, we will search for all employees who have a manager ID equal to the employee IDs found in the previous steps (hence the connection with the hierarchy table).

A few words of explanation should also be given to the field called level. This field has a value 0 for the element we start the search with – so we can call it a zero level element. For each subsequent step of recursion, the value of this field is increased (but only once within the „loop” and not for each record), which allows us to visualize at which level of recursion a given record was found.

The effect of the above code will be the following set of records:

We can see that the records of the person with whom we started the search and the records of all this person’s subordinates have been found. If we additionally modified our query by eliminating the records from level 0, the effect would be a list of all Mr. Julian’s subordinates.

It is not difficult to guess that the effect we have achieved is the implementation of the top-down approach. If we wanted to achieve the effect of the bottom-up approach now, our above query would have to be slightly modified. To show it on an example, we will invite Mrs. Amelia Przybylska (the person with ID 4) from the company presented above:

declare @person int = 4;
with hierarchy as (
     select *, 0 as level
     from @workers
     where idWorker = @person
     union all
     select p.*, (level + 1) as level
     from @workers as p
     join hierarchy as h on h.idManager = p.idWorker
 )
 select *
 from hierarchy;
 

If someone did not notice what has changed in relation to the previous query, I suggest that it is about line 9 where I swapped the aliases h and p with each other at the comparison operation. In other words, this time we are looking for managers of all workers found previously (in this report, only one person will be found in each step of the recursion because each person has only one manager).

Our modified code will result in the following set of records:

If, as before, we eliminated the records from level 0, the effect would be a list of all managers standing higher in the hierarchy than Mrs. Amelia.

As you can see in the above examples, building a hierarchy with recursion is not as trivial as it may seem, and even a potentially small change in the query itself can lead to completely different results than we would expect. I noticed that the entries devoted to recursion in T‑SQL often focus on building a hierarchy only in one direction because in my opinion they treat it only as a tool to show the CTE operation, thus losing the very essence of the problem being solved. From my standpoint, the problem to be solved should be more important than the tool and therefore I believe that the CTE should be presented as a tool to achieve the goal, not a goal itself.

I hope that despite the banal topic, this post turned out to be useful for someone 🙂

If, dear reader, you are interested in the topic of recursion in T‑SQL and would like to see what else this mechanism can be used for, I invite you to read my other post on preparing data for reports.