MYSQL - 从父母那里获得所有孩子

Question

I have stuck in getting all children's query where parent id greater than the customer id

table test

  id    name    parent
    1   test1   0
    2   test2   1
    3   test3   1
    4   test4   2
    5   test5   2
    6   test6   10
    7   test7   10
    8   test8   6
    9   test9   6
    10  test10  5
    11  test10  7

Currently I am using this recursive query but it shows children till the 10 parent but not able to give children of 6 and 7 and further

SELECT id , parent FROM (SELECT  id , parent from (SELECT * FROM test order by
parent , id) testdata_sorted, (SELECT @pv := '1') initialisation where 
find_in_set(parent , @pv) > 0 and @pv := concat(@pv, ',', id)  ORDER BY 
parent ASC) AS tt

Current Output is ->

id  parent
2   1
3   1
4   2
5   2
10  5
6   10
7   10

I need this Type of output . I need help out in this regard .

id  parent
2   1
3   1
4   2
5   2
10  5
6   10
7   10
8   6
9   6
11  7

Answer 1

You are using a fragile way to simulate a recursive query. It specifically requires that a parent row has to be sorted before the child.

Your base rowset is using order by parent, id:

id  parent
----------------------
1   0
2   1         -- fine
3   1         -- fine 
4   2         -- fine
5   2         -- fine 
10  5         -- fine 
8   6         -- parent 6 comes later!
9   6         -- parent 6 comes later! 
11  7         -- parent 7 comes later!
6   10        -- fine
7   10        -- fine

You see that those are exactly the rows that are missing from your result.

There is no simple fix to this, as to order your rows on the fly to be able to be used in your recursive query, you need a recursive query. You may be able to enter your data in a way that fulfills that condition though. While I assume that the part where parent id greater than the customer id in your question is actually not a condition (as your expected output does not align with that): if you have such a condition that constraints parent and child, it could give you a possible order.

For alternative ways to model your data or write your query, see How to create a MySQL hierarchical recursive query. Actually, trincots answer includes a remark about the order requirement for your code.

Preferably, you would be using a version that supports recursive CTEs, because as long as you do not want to change your data model, every workaround for those has some limitations (e.g. row order or max depth).

A side note: order by in a subquery (specifically testdata_sorted) can be ignored by MySQL, and you may have to verify that it doesn't (which can depend on things like version, indexes or table sizes).