Analyze the family tree
1. Analyze the family tree
In the last exercises, we have learned how to use recursive queries and how we can apply basic principles to work with these queries. Now we are going to look at a new example, the family tree.2. The family tree
A family tree tells us about the generations within a family history. It shows us the parents, grandparents, or children across history. A family tree can look like the example presented on the right side of the slide. It is actually a hierarchical data model and can be queried with a recursive CTE. For the following tasks, we are using the table familyTree which consists of an ID representing the current generation, the name of the person and the ID of one parent. The fields describing the hierarchy are the fields ID and parentID.3. Putting it all together
Before we start coding, let's recap the things we have learned so far. Remember the following principles about recursive queries. At the start, initialize the recursion by the anchor member. This query is executed once. This step is followed by the recursion member executing the recursion function. This step is repeated until the termination condition is met. To work with recursive CTEs, we also know two common tasks. These are on the one side to keep track of the current recursion level and on the other side to combine the result of the recursion into one field. We are going to apply these principles on two examples on the family tree.4. Questions about the family tree
First, we want to get the number of generations. We want to get the total number of generations for a desired person ID. To solve this task we have to use the level of recursion. The level is initialized in the anchor member and is increased in the recursive member. Then we have to count the number of levels representing the number of generations. A possible result could be, for example, 100, meaning that a certain person has had 100 generations up to now.5. Questions about the family tree
Second, we want to combine all possible parents into one field. We want to get the father, grandfather, and so on. To solve this task, we first have to keep track of the parents for each iteration. We have to initialize this field in the anchor member by the ID of the child and add the current parent ID each recursion step. Next, we also have to keep track of the number of recursions. The result could be, for example, that Dominik Egarter has the parents history of persons with the IDs 100, 101, 102, and 103.6. Let's check the family tree!
Now you have time to apply your knowledge. Let`s practice!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.