mysql query get hierarchical data

Question

I have data like this

and using this query I got right output

SELECT  id,
        nama,
        parent_id
FROM    (SELECT * FROM members
         ORDER BY parent_id, id) members,
        (SELECT @pv := '3') initialisation
WHERE   FIND_IN_SET(parent_id, @pv) > 0
AND     @pv := CONCAT(@pv, ',', id)

output :

but if I modified parent_id of member14 from parent 13 to 15, I got wrong output

output : (less than 1 column that appears (Member14))

expect output :

id     nama     parent_id
8      Member8   3
13     Member13  8
15     Member15  13
14     Member14  15

Please include data as SQL or markdown tables, not images. Seems to work as expected - [db<>fiddle](https://dbfiddle.uk/IWHE-WMi). If using MySQL >= 8 you should switch to a [recursive cte](https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive-hierarchy-traversal). — user1191247, Dec 09 '22 at 12:11
oh yeah thanks. but why in your `db<>fiddle` the last result can output `Member14`, but if I run query in mysql, the `Member14` is not show? — Eggy, Dec 09 '22 at 12:18
What version of MySQL are you running? Please add your CREATE TABLE statement to your question. — user1191247, Dec 09 '22 at 12:28

score 1 · Answer 1 · answered Dec 09 '22 at 11:37

1

That's because @pv variable is processed for each row and it did this respecting natural order of the table (id values). You can add @pv in result

id  name            pv
8   Member8     3   3,8
13  Member13    8   3,8,13
15  Member15    13  3,8,13,15

So when on 14 Member @pv don't have 15 id in it. So the constraint is that parent_id need to be less than id for each row.

answered Dec 09 '22 at 11:37

Mr_Thorynque

1,749
1
20
31

yes, but can I still do without select a smaller parent_id? like my case – Eggy Dec 09 '22 at 12:09
https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query – Mr_Thorynque Dec 09 '22 at 14:23

mysql query get hierarchical data

1 Answers1