Get Immediate parent and root parent for child in oracle select query-CodePudding

My input is like this

and i need output like this

I tried using join/Left joins but its showing only immediate parent. like below

SELECT ChildUserType.Id as child, ParentUserType.Id as parent 
FROM tablea AS ChildUserType
LEFT JOIN tablea AS ParentUserType 
  ON ChildUserType.main_Line_id = ParentUserType.Id;

anyone has any idea how i can get the desired output

CodePudding user response：

Working example :

create table table1 (child int, parent int);
insert into table1 values (1, null);
insert into table1 values (2, 1);
insert into table1 values (3, 2);
insert into table1 values (4, 3);
insert into table1 values (5, null);
insert into table1 values (6, 5);
insert into table1 values (7, 6);

select * from table1

SELECT T1.child_id, T2.parent immediate_parent_id, root_id, level_
FROM (
  SELECT 
      CONNECT_BY_ROOT t1.child AS CHILD_ID,
      t1.child AS ROOT_ID,
      level AS level_ 
  FROM 
      table1 t1
  WHERE 
      t1.parent is null
  CONNECT BY t1.child = PRIOR t1.parent
) T1
JOIN table1 T2 on T2.child = T1.child_id

CodePudding user response：

With the data from your question - you can try this:

SELECT
    t.CHILD "CHILD",
    t.PARENT "IMMEDIATE_PARENT",
    t1.PARENT "ROOT_PARENT"
FROM
    tbl t
INNER JOIN
    tbl t1 ON(t1.CHILD = t.PARENT)
ORDER BY
    t.CHILD
--  
--  R e s u l t
--
--       CHILD IMMEDIATE_PARENT ROOT_PARENT
--  ---------- ---------------- -----------
--        2669             2664        2625 
--        2670             2664        2625