MySQL retrieve full descendants from given parent ID

Question

I'm struggling to create a single query to retrieve all descendants for a given parent id. I have the following table structure:

menu_id  |  parent_id  |  title
1           0             Text 1
2           0             Text 2
3           2             Text 2.1
4           2             Text 2.2
5           2             Text 2.3
6           0             Text 3
7           6             Text 3.1
8           6             Text 3.2
9           6             Text 3.3
10          6             Text 3.4
11          10            Text 3.4.1
12          10            Text 3.4.2
13          10            Text 3.4.3
14          10            Text 3.4.4

I want to achieve the following result, for given parent id 6:

menu_id
6
7
8
9
10
11
12
13
14

Yet, I'm not able to go trough all descendants, but to the first node.. This is the query that I managed to create until now:

    SELECT T2.menu_id
    FROM (
        SELECT
            @r AS _id,
            (SELECT @r := menu_id FROM bo_admin_menu WHERE parent_id = _id) AS menu_id,
            @l := @l + 1 AS lvl
        FROM
            (SELECT @r := 6, @l := 0) vars,
            bo_admin_menu h
        WHERE @r <> 0) T1
    JOIN bo_admin_menu T2
    ON T1._id = T2.parent_id
    ORDER BY T1.lvl DESC

Could someone point me to the correct path?

The table is just a illustration of the actual database, so it could have been the parent id 200.. It's irrelevant the number itself but the requirement it's the same, get all childs and descendants for given node — rosuandreimihai, Mar 10 '17 at 21:23
You've spotted that MySQL has no native support for recursion. Options include writing a sproc, joining the table to itself as often as could be required, handling the logic in application level code, or switching to a different model - e.g. nested sets. (Although in your particular case, you also have the choice of a materialised path) — Strawberry, Mar 11 '17 at 00:00

score -2 · Answer 1 · answered Mar 10 '17 at 21:36

-2

In your case, why not just use like on the title column (ignoring parent column), like so:

select menu_id 
from bo_admin_menu t2 join bo_admin_menu t1 on 
  t2.`title` like CONCAT(t1.title, '%')
where t1.menu_id = 6
order by t2.menu_id

answered Mar 10 '17 at 21:36

Yossi Vainshtein

3,845
4
23
39

And how do you pass from one upper node to another lower one? – rosuandreimihai Mar 10 '17 at 21:38
I got it, you are literally taking the title for good.. the title doesn't preserve those namings.. – rosuandreimihai Mar 10 '17 at 21:39
I don't realy traverse the heirarchy, I just use the `title` column, assuming `title` of parent is 'Text 3' (for example), I'm just querying with `title` like 'Text 3%', returning 3, 3.1 and so on – Yossi Vainshtein Mar 10 '17 at 21:40
OK, maybe you should then modify the question (it's not clear that you can't rely on this). I'm afraid in MySQL there's no good solution for this if the depth of recursion is unlimited. (other DBs like Oracle and SQLServer have hierarchical query features). But this question has already been asked and aswered, check here: http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query – Yossi Vainshtein Mar 10 '17 at 21:45

MySQL retrieve full descendants from given parent ID

1 Answers1