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. If using MySQL >= 8 you should switch to a recursive cte. — user1191247
– user1191247, Commented Dec 9, 2022 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
– Eggy, Commented Dec 9, 2022 at 12:18
What version of MySQL are you running? Please add your CREATE TABLE statement to your question. — user1191247
– user1191247, Commented Dec 9, 2022 at 12:28

Mr_Thorynque · Accepted Answer · 2022-12-09 11:37:37Z

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 9, 2022 at 11:37

Mr_Thorynque

2,0621 gold badge26 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eggy Over a year ago

yes, but can I still do without select a smaller parent_id? like my case

Mr_Thorynque Over a year ago

stackoverflow.com/questions/20215744/…

Collectives™ on Stack Overflow

mysql query get hierarchical data

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related