0

I have a table "family_tree". It has three columns "id", "parent_name", parent_id". 

--id--             --parent_name--    --parent_id--
1                  Parent 1              Null
2                  Parent 2               1
3                  parent 3               2

The family tree for id 3 would be 3-->2-->1 . Is it possible to to have a recursive SQL query to find the family tree line for a given id using SQL? If it is possible how do I write the query?

user1986244
  • 259
  • 2
  • 12
  • 2
    https://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql – jose_bacoy Mar 13 '18 at 23:19
  • 1
    MySQL doesn't support [common table expressions (CTEs)](https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL), so probably the best way to do it is in a stored procedure like the answers in the link provided by anonyXmous. – Racil Hilan Mar 13 '18 at 23:27
  • @Racil and anonyXmous thanks I have looked one of the links previously but I found the query to be confusing. I will attempt to follow the solutions – user1986244 Mar 13 '18 at 23:31
  • 1
    The trick is to unroll the recursion. Create a temp table and insert all the rows where the parentid is null. then in a while loop interactively insert into the temp table all of the children of the items in the temp table until the insert operation adds nothing more, then you know you have added all the descendants. – JohnFx Mar 13 '18 at 23:36
  • 1
    http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ – Barmar Mar 13 '18 at 23:50

0 Answers0