MySQL Family Tree Recursive Query (version 5.6)

192 views Asked by At

I am trying to get a query that returns a family tree from the very first ancestor to their last descendant. I am using MySQL version 5.6, and here is the query I came up with:

 SELECT   
            p1.individual_id as ParentId,
            p1.first_name as ParentName,
            p2.first_name as Child1_Name,
            p3.first_name as Child2_Name,
            ...
            p10.first_name as Child9_Name,
 FROM individual p1
            LEFT JOIN individual AS p2 ON p2.parent = p1.individual_id
            LEFT JOIN individual AS p3 ON p3.parent = p2.individual_id
            LEFT JOIN individual AS p4 ON p4.parent = p3.individual_id
            ....
            LEFT JOIN individual AS p10 on p10.parent = p9.individual_id
 WHERE 1 IN (p1.parent,
            p2.parent,
            p3.parent,
            ...
            p10.parent)

Here is the output I am getting. I am not sure how to change the query so that it returns just one line with the very first ancestor and all their children. enter image description here

For example, you can see on the screenshot that the parent has different children in Child2_Name column ("Ozubek" and "Kaiyr"), thus we get an additional line for "Ozubek" descendants and Kaiyr descendants respectively. However, I want them both to be in Child2_Name column, not separately. The same is for the rest of the children.

Is it possible to do what I want and group all the children in kind of array? I know I could use the concat, but in that way the children would be a string type separated by commas instead of being an actual object, which makes it difficult for me to use the query results for displaying it in React application...

0

There are 0 answers