question

rino_abraham avatar image
rino_abraham asked

Stored procedure to find child nodes of a tree.

1 ________2________3_______6 | | | |______7 | | |______4________8 | | | |______9 | | |______5 Create Table OBJECT_TREE ( PARENT_ID int, CHILD_ID int ) OBJECT_TREE PARENT_ID CHILD_ID 1 2 2 3 2 4 2 5 3 6 3 7 4 8 4 9 the procedure to give only leaves (objects with no children) (all leaves objects of node 2 are 5, 6, 7, 8, 9) Please help to find the leaves.
procedurestored
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
This is a good candidate for a recursive cte Take a look at this code. The first cte is simply defining the possible list of leaves, i.e. all those `child_id`s that are not also `parent_id`s - this then becomes our starting point for the recursion, and we work our way up the hierarchy, finding all the possible parents. If you run the final query but without the where clause you will see the full set of parent/leaf combinations ;with leaves_cte as ( select ot1.child_id from object_tree ot1 left join object_tree ot2 on ot1.child_id = ot2.parent_id where ot2.parent_id is null ), cte as ( select leaves_cte.child_id as leaf, ot1.parent_id from object_tree ot1 join leaves_cte on ot1.child_id = leaves_cte.child_id union all select cte.leaf, ot1.parent_id from cte join object_tree ot1 on cte.parent_id = ot1.child_id ) select parent_id, leaf from cte where parent_id = 2 order by leaf
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thnx.... Kev dat was awesome..thnxx
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.