Get all the children of parents MySQL

Question

I have a sample MySQL table with the columns ID, NAME AND ID_PARENT.

ID	NAME	ID_PARENT
1	NODE 1	NULL
2	NODE 2	1
3	NODE 3	7
4	NODE 4	2
5	NODE 5	10
6	NODE 6	5
7	NODE 7	5
8	NODE 8	7
9	NODE 9	6
10	NODE 10	1
11	NODE 11	NULL
12	NODE 12	11
13	NODE 13	14
14	NODE 14	10
18	NODE 15	14

If I want to get the children of Node 1 then I will get the following.

     NODE 1
    /      \
NODE 2     NODE 10
  |          |
NODE 4       |__________________
             |                 |
           NODE 5              |
            /   \              |
       NODE 6   NODE 7       NODE 14
         |         |        /     \
       NODE 9   NODE 3  NODE 15   NODE 13

If you think why the NODE 7 is the parent of NODE 3. The answer is that we can modify parents after the creation of all nodes.

I am using the following SQL query and it cannot get all the nodes. But, if the id_parent data is in asc order within the table then it works fine.

SELECT id,
       node_name,
       id_parent
FROM   tree
WHERE  id=1
UNION
SELECT id,
       node_name,
       id_parent
FROM   (
                SELECT   id,
                         node_name,
                         id_parent
                FROM     tree t
                ORDER BY t.id_parent ) tree,
       (
              SELECT @pv := 1) initialisation
WHERE  Find_in_set(id_parent, @pv) > 0
AND    @pv := concat(@pv, ',', id);

By running the above SQL I get the following nodes.

     NODE 1
    /      \
NODE 2     NODE 10
  |          |
NODE 4       |___________
             |          |
           NODE 5       |
                        |
                      NODE 14
                      /     \
                  NODE 15   NODE 13

So, how can I modify the query so that I get all the nodes as demonstrated in the first NODES representation?

MySQL 8.0 supports [recursive CTE queries](https://dev.mysql.com/doc/refman/8.0/en/with.html). That's the preferred solution, and it's worth upgrading to 8.0 if you have not done so. But if you must use an earlier version of MySQL, see my answer to [What is the most efficient/elegant way to parse a flat table into a tree?](https://stackoverflow.com/a/192462/20860) or my presentation [Models for hierarchical data](https://www.slideshare.net/billkarwin/models-for-hierarchical-data) — Bill Karwin, Mar 09 '22 at 18:33

score 0 · Answer 1 · answered Mar 09 '22 at 19:03

There are quite a few 'models' of storing the hierarchical data in relational model, each with it's own pros and cons.

The model you've presented (the adjacency list) is the easiest to understand, but also the most limiting.

This article offers a great overview of viable solutions https://www.databasestar.com/hierarchical-data-sql/ so you can weight the adequacy of each approach to your requirements.

Get all the children of parents MySQL

1 Answers1