Analyze the family tree

Hierarchical and Recursive Queries in SQL Server

Jasmin Ludolf

Content Developer

The family tree

The familyTree is described by:

ID of the person
Name of the person
parentID the ID of the parent

The elements describing the hierarchy:

ID
parentID

Image of a family tree

Putting it all together

Remember the following principles about recursive CTEs:

Initialize the recursion in the anchor member
Implement the recursion function in the recursion member
Define a termination condition

Remember the following working principles:

Get the level of recursion
Combine the recursion function into one field

Questions about the family tree

Get the number of generations

Define the LEVEL

-- Anchor member
0 as LEVEL
-- Recursive member
LEVEL + 1

Count the number of LEVELS to get generations COUNT(LEVEL)

Generations:
100

Questions about the family tree

Get all possible parents in one field

Combine recursion results into one field

-- Anchor member
CAST(ID AS VARCHAR(MAX)) as Parent
-- Recursive member
Parent + ' -> ' + CAST(parentID AS VARCHAR(MAX))

+---------------------------------------------+
| Name            | Parent                    |
|-----------------|---------------------------|
|Dominik Egarter  | 100 -> 101 -> 102 ->103   |
+---------------------------------------------+

Let's check the family tree!

Hierarchical and Recursive Queries in SQL Server