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

user_12177405 0
Viewed 50

My input is like this

enter image description here

and i need output like this

enter image description here

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

hierarchical-data oracle sql
3 Answers

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

Content of 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

Child, immediate parent and root

user_14444910

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
user_190233530

You can do this with a recursive CTE:

CREATE TABLE RELATIONSHIPS (
  CHILD NUMBER(4),
  PARENT NUMBER(4));
  
  INSERT INTO RELATIONSHIPS VALUES (2660,2648);
  INSERT INTO RELATIONSHIPS VALUES (2662,2625);
  INSERT INTO RELATIONSHIPS VALUES (2663,2661);
  INSERT INTO RELATIONSHIPS VALUES (2664,2625);
  INSERT INTO RELATIONSHIPS VALUES (2665,2661);
  INSERT INTO RELATIONSHIPS VALUES (2666,2625);
  INSERT INTO RELATIONSHIPS VALUES (2667,2625);
  INSERT INTO RELATIONSHIPS VALUES (2668,2625);
  INSERT INTO RELATIONSHIPS VALUES (2669,2664);
  INSERT INTO RELATIONSHIPS VALUES (2670,2664);
  INSERT INTO RELATIONSHIPS VALUES (2672,2671);


WITH RELATIONSHIPS_W_ROOT(CHILD,PARENT,ROOT) AS (
  SELECT CHILD,PARENT,PARENT ROOT FROM RELATIONSHIPS
  UNION ALL
  SELECT X.CHILD,X.PARENT,T.ROOT FROM RELATIONSHIPS_W_ROOT T
  JOIN RELATIONSHIPS X ON (T.CHILD=X.PARENT)
  )
SELECT * FROM RELATIONSHIPS_W_ROOT;

To get just the 2 rows you posted as output add WHERE PARENT <> ROOT.

See Sql Fiddle.

user_3208090
Related