CODEWITHSUNDEEP
sqloracle-databaseparent-child
Sachin Prajapati
Sachin Prajapati

Reputation: 1

Get parent its silbilng in sql oracle

Given Parent-child relationship. I want to get all parents and silbling of given childId

parentId |  chilId 

--------------------
null     |   Node0

Node0    |   Node1

Node0    |   Node2

Node1    |   Node3

Node1    |   Node4

Node2    |   Node5

Node3    |   Node6

Node3    |   Node7

null     |    C0

C0       |    C1

C0       |    C3

C3       |    C1

---------------------

Suppose if choose childID as Node6 I should get following results:

null     |   Node0

Node0    |   Node1

Node0    |   Node2

Node1    |   Node3

Node1    |   Node4

Node2    |   Node5

Node3    |   Node6

Node3    |   Node7

Upvotes: 0

Views: 41

Answers (1)

Popeye
Popeye

Reputation: 35910

I think you can achieve it using connect by prior and some functions as following:

Passing Node6 as input parameter, used in WHERE clause at two places

SQL> WITH DATA (PARENT, CHILD) AS
  2  (SELECT null , 'Node0'   FROM DUAL UNION ALL
  3  SELECT 'Node0', 'Node1' FROM DUAL UNION ALL
  4  SELECT 'Node0', 'Node2' FROM DUAL UNION ALL
  5  SELECT 'Node1', 'Node3' FROM DUAL UNION ALL
  6  SELECT 'Node1', 'Node4' FROM DUAL UNION ALL
  7  SELECT 'Node2', 'Node5' FROM DUAL UNION ALL
  8  SELECT 'Node3', 'Node6' FROM DUAL UNION ALL
  9  SELECT 'Node3', 'Node7' FROM DUAL UNION ALL
 10  SELECT null , 'C0' FROM DUAL UNION ALL
 11  SELECT 'C0', 'C1'  FROM DUAL UNION ALL
 12  SELECT 'C0', 'C3'  FROM DUAL UNION ALL
 13  SELECT 'C3', 'C1'  FROM DUAL),
 14  DATA_ACTUAL AS
 15  (SELECT PARENT,
 16          CHILD,
 17          CONNECT_BY_ROOT CHILD ROOT_,
 18          SYS_CONNECT_BY_PATH(PARENT, '/') CP FROM DATA
 19   CONNECT BY PRIOR CHILD = PARENT START WITH PARENT IS NULL)
 20  --
 21  SELECT PARENT,
 22         CHILD
 23    FROM DATA_ACTUAL
 24   WHERE ROOT_ = (SELECT ROOT_ FROM DATA_ACTUAL WHERE CHILD = 'Node6')
 25  AND CP NOT LIKE (SELECT '%' || PARENT || '/%' FROM DATA_ACTUAL WHERE CHILD = 'Node6');

PAREN CHILD
----- -----
      Node0
Node0 Node1
Node1 Node3
Node3 Node6
Node3 Node7
Node1 Node4
Node0 Node2
Node2 Node5

8 rows selected.

SQL>

Passing the input parameter as Node2:

SQL> WITH DATA (PARENT, CHILD) AS
  2  (SELECT null , 'Node0'   FROM DUAL UNION ALL
  3  SELECT 'Node0', 'Node1' FROM DUAL UNION ALL
  4  SELECT 'Node0', 'Node2' FROM DUAL UNION ALL
  5  SELECT 'Node1', 'Node3' FROM DUAL UNION ALL
  6  SELECT 'Node1', 'Node4' FROM DUAL UNION ALL
  7  SELECT 'Node2', 'Node5' FROM DUAL UNION ALL
  8  SELECT 'Node3', 'Node6' FROM DUAL UNION ALL
  9  SELECT 'Node3', 'Node7' FROM DUAL UNION ALL
 10  SELECT null , 'C0' FROM DUAL UNION ALL
 11  SELECT 'C0', 'C1'  FROM DUAL UNION ALL
 12  SELECT 'C0', 'C3'  FROM DUAL UNION ALL
 13  SELECT 'C3', 'C1'  FROM DUAL),
 14  DATA_ACTUAL AS
 15  (SELECT PARENT,
 16          CHILD,
 17          CONNECT_BY_ROOT CHILD ROOT_,
 18          SYS_CONNECT_BY_PATH(PARENT, '/') CP FROM DATA
 19   CONNECT BY PRIOR CHILD = PARENT START WITH PARENT IS NULL)
 20  --
 21  SELECT PARENT,
 22         CHILD
 23    FROM DATA_ACTUAL
 24   WHERE ROOT_ = (SELECT ROOT_ FROM DATA_ACTUAL WHERE CHILD = 'Node2')
 25  AND CP NOT LIKE (SELECT '%' || PARENT || '/%' FROM DATA_ACTUAL WHERE CHILD = 'Node2');

PAREN CHILD
----- -----
      Node0
Node0 Node1
Node0 Node2

SQL>

Cheers!!

Upvotes: 1

Related Questions