Functions for parent-child hierarchies

The last group of functions we will look at in this chapter is the one that helps to deal with a parent-child relationship in a table. A common scenario for this is an employee table, where one employee record may be related to another, for example where an employee has a manager.

Figure 6-9 shows the table we will be working with as an example. All employees other than the CEO have a parent employee ID that references their manager's employee ID:

Figure 6-9: The employees table

This can be illustrated by converting it into an organization chart. As you can see in the diagram shown in Figure 6-10, we have a hierarchy that consists of four levels, with the CEO at the top:

Figure 6-10: Organization chart showing employee hierarchy

In this situation, we hit a problem with the Tabular data model. It is simply not possible to create a circular...