sql - Determine (root), (root+1), (root+2) in hierarchical query

Question

I've got hierarchical data in Oracle. Each Row has an id, parent_id and name.

For each row I want to get

• the root ID (this is straightforward using connect_by_root)
• the ID of the node one level above root
• the ID of the node two levels above root

My baseline is

SELECT
    id,
    parent_id,
    name,
    connect_by_root id root,
    <child_of_root_in_this_exact_path> child1_id,
    <child_of_the_child_above> child2_id,
FROM table
    START WITH id = 1
    CONNECT BY PRIOR id = parent_id

The parent and grandparent of the child would be trivial (wouldn't need a hierachical query at all for this), but how do I find specific nodes from the opposite direction?

Answer 1

It seems that, given any ID, you want to find it, all its children, and all its grandchildren. You didn't quite show how you plan to distinguish them in the output; here is one way:

select  id, case level when 1 then 'Self' 
                       when 2 then 'Child' 
                       when 3 then 'Grandchild' end as generation
from    table
start   with id = 1
connect by id = prior parent_id
where   level <= 3
;