interconnected data with recursive hierarchy

Question

I have a table Relatives as below

| Name  | Id  | Rel_Id  |
--------------------------
|  A    |  1  |  2      | 
|  B    |  1  |  3      | 
|  C    |  1  |  4      | 
|  D    |  5  |  1      | 
|  E    |  6  |  1      | 
|  F    |  7  |  2      | 
|  G    |  2  |  8      | 
|  H    |  9  |  8      | 
|  I    |  10 |  11     |

I am looking to fetch all relatives of 1 and then recursively their relatives present in either Id column or Rel_id column. So for id OR rel_id = 1, I intend to pull A, B, C, D, E, F, G, H but not I.

I tried using oracle hierarchy sql in oracle 12c.

select 
dd.*
from relatives dd
start with id = 1
connect by   id = prior rel_ID
union 
select 
dd.*
from relatives dd
start with rel_ID = 1
connect by   Id =  prior rel_ID;

It is not pulling the subsequent relative Ids (F or H).

You wrote exactly the same SELECT statement twice. One of them should have connect by rel_id = prior id. — user5683823
– user5683823, Commented Aug 23, 2019 at 23:17
However, note that this only finds direct descendants and direct ancestors, it doesn't find all the relatives of the relatives of 1. For example, what if you also had a row with values J 5 20? 5 is related to 1, and 5 is also related to 20, but you wouldn't find 20 even in the corrected query. Is that what you do, in fact, need? That is a much harder question than you think. — user5683823
– user5683823, Commented Aug 23, 2019 at 23:19
Hi @mathguy Yes I’m looking to fetch then 20 also. If 1 is related to 5 and 5 is related to 20 then 20 also. — user1582625
– user1582625, Commented Aug 24, 2019 at 3:14
Just curious, SQL server solves this with recursive CTE statements, is there no equivalent in Oracle? — Charleh
– Charleh, Commented Aug 24, 2019 at 11:35

Popeye · Accepted Answer · 2019-08-24 08:54:25Z

You can achieve this using multiple OR condition in CONNECT BY clause as following:

SQL> WITH DATAA ( Name, Id, Rel_Id)
  2  AS
  3  (
  4  SELECT 'A', 1, 2 FROM DUAL UNION ALL
  5  SELECT 'B', 1, 3 FROM DUAL UNION ALL
  6  SELECT 'C', 1, 4 FROM DUAL UNION ALL
  7  SELECT 'D', 5, 1 FROM DUAL UNION ALL
  8  SELECT 'E', 6, 1 FROM DUAL UNION ALL
  9  SELECT 'F', 7, 2 FROM DUAL UNION ALL
 10  SELECT 'G', 2, 8 FROM DUAL UNION ALL
 11  SELECT 'H', 9, 8 FROM DUAL UNION ALL
 12  SELECT 'I', 10, 11 FROM DUAL
 13  )
 14  SELECT DISTINCT *
 15  FROM
 16      DATAA
 17  START WITH ID = 1
 18  CONNECT BY ( PRIOR ID = ID OR PRIOR ID = REL_ID
 19               OR PRIOR REL_ID = ID OR PRIOR REL_ID = REL_ID )
 20             AND PRIOR NAME < NAME -- used this condition to avoid looping
 21  ORDER BY NAME;

N         ID     REL_ID
- ---------- ----------
A          1          2
B          1          3
C          1          4
D          5          1
E          6          1
F          7          2
G          2          8
H          9          8

8 rows selected.

SQL>

Cheers!!

Collectives™ on Stack Overflow

interconnected data with recursive hierarchy

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related