CODEWITHSUNDEEP
sqloracle-database
user2242044
user2242044

Reputation: 9213

Recursively grabbing parent nodes - SQL

I have a table with 5 nodes. A Great Grandparent, Grandparent, parent, child 1, child 2.

I have the parent node which is node_id = 10. I would like to return the entire hierarchy. I wrote this function which recursively grabs everything below the given node, but I can't find a keyword that is opposite Prior to go up the node structure.

Select node_id, name, Parent_node_id, Prior name
From my_schema.my_table
Start With node_id = '10'
Connect by Prior node_id = parent_node_id;

This is being done through Oracle.

Upvotes: 0

Views: 51

Answers (2)

Sentinel
Sentinel

Reputation: 6449

Just move prior to the other side of the connect by cluase:

Select node_id, name, Parent_node_id, Prior name
From my_schema.my_table
Start With node_id = '10'
Connect by node_id = prior parent_node_id;

Upvotes: 1

sstan
sstan

Reputation: 36483

You don't need a different keyword. To go up the hierarchy, just invert node_id and parent_node_id in the connect by clause:

Select node_id, name, Parent_node_id, Prior name
From my_schema.my_table
Start With node_id = '10'
Connect by Prior parent_node_id = node_id;

Upvotes: 1

Related Questions