In this post, we will see how to resolve Recursive CTE for a family tree keeps going into an infinite loop
I am trying to design a RDBM model for a family tree using SQL Server and currently I have the 3 tables that looks somewhat as follows
Stores basic details of members of the family.
Stores the Members.ID of the patners that were together
Stores the Families.ID of each family and the Member.ID of the children of that family.
What I want now is to use a recursive CTE to travese the tree given a member ID. So if I give a member ID as 1007, I want to find all families they are part of and then their children’s families till the last node in the that particular tree.
This is the query I have so far
The first part of the CTE (family_tree) is pretty straight forward and returns the family where the member with ID 1007 is part of.
In the union, I am trying to recurse through the Families table again for each row returned by the family_tree where the child in the family is either spouse1 or spouse2. But the entire thing just keeps going into an infinite loop and I can’t figure out what’s wrong with the termination clause. Any help would really be appreciated.
I took siggemannen
‘s advice and updated the DB model to have a single Member_Parents table
and was able to solve the problem pretty quickly as follows
The query turned out to be pretty straight forward without too many joins. I also agree that my CTE was incorrect and I guess it was due to my understanding of the recursive CTE (learnt it just a couple of days back).
It would really be appreciated if you could review this query.
I am now trying to figure out a way to fetch the ancestors for a given member (traverse up the tree).
Was able to quickly get the ancestor tree as well for a give member
If you have better answer, please add a comment about this, thank you!