MySQL Forums
Forum List  »  Newbie

recursive CTE - hierarchy with parent, child, next
Posted by: Gabrielle Laurent
Date: November 28, 2022 06:35AM

Hello,

I have a hierarchy with parent, child and next id. The node_id of the hierarchy is not incremental, so my rows are sorted randomly. I need to query the hierarchy and sort it, I know I should use a recursive CTE but I have no idea how....
Could you help me ?

so I have this table :
CREATE TABLE GLT
(Node_id INT,
parent INT,
next INT,
child INT,
label VARCHAR(50));


INSERT INTO GLT (node_id, parent, next, child, label) VALUES
( 3 , 4 , 11 , 0 , 'A21' ),
( 4 , 8 , 0 , 3 , 'A2' ),
( 5 , 0 , 0 , 9 , 'C' ),
( 6 , 8 , 0 , 0 , 'A3' ),
( 7 , 9 , 25 , 0 , 'C11' ),
( 8 , 0 , 21 , 19 , 'A' ),
( 9 , 5 , 0 , 7 , 'C1' ),
( 10 , 21 , 0 , 0 , 'B1' ),
( 11 , 4 , 0 , 0 , 'A22' ),
( 19 , 8 , 4 , 0 , 'A1' ),
( 21 , 0 , 5 , 10 , 'B' ),
( 25 , 9 , 0 , 0 , 'C12' )


node_id / Parent / Next / Child / label
3 / 4 / 11 / 0 / A21
4 / 8 / 0 / 3 / A2
5 / 0 / 0 / 9 / C
6 / 8 / 0 / 0 / A3
7 / 9 / 25 / 0 / C11
8 / 0 / 21 / 19 / A
9 / 5 / 0 / 7 / C1
10 / 21 / 0 / 0 / B1
11 / 4 / 0 / 0 / A22
19 / 8 / 4 / 0 / A1
21 / 0 / 5 / 10 / B
25 / 9 / 0 / 0 / C12



and I would like the query to order it like this :

node_id / Parent / Next / Child / label
8 / 0 / 21 / 19 / A
19 / 8 / 4 / 0 / A1
4 / 8 / 0 / 3 / A2
3 / 4 / 11 / 0 / A21
11 / 4 / 0 / 0 / A22
6 / 8 / 0 / 0 / A3
21 / 0 / 5 / 10 / B
10 / 21 / 0 / 0 / B1
5 / 0 / 0 / 9 / C
9 / 5 / 0 / 7 / C1
7 / 9 / 25 / 0 / C11
25 / 9 / 0 / 0 / C12


and eventually add a column with an order sequence.

How should I do this ?

Thanks for your help

Gab

Options: ReplyQuote


Subject
Written By
Posted
recursive CTE - hierarchy with parent, child, next
November 28, 2022 06:35AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.