CODEWITHSUNDEEP
mysql
Eggy
Eggy

Reputation: 551

mysql query get hierarchical data

I have data like this

enter image description here

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 :

enter image description here

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

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

enter image description here

expect output :

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

Upvotes: 1

Views: 385

Answers (1)

Mr_Thorynque
Mr_Thorynque

Reputation: 2002

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.

Upvotes: 1

Related Questions