question

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