CODEWITHSUNDEEP
sqloracle-databaseplsql
Abhi
Abhi

Reputation: 55

How to get unique hierarchy on table dependencies

I have created one table,which contains two column "Source" and "Target". I have cases in which some values in the target act as source to some other values like

+--------+--------+
| Source | target |
+--------+--------+
| A      | E      |
+--------+--------+
| B      | D      |
+--------+--------+
| C      | F      |
+--------+--------+
| D      | C      |
+--------+--------+
| E      | F      |
+--------+--------+
| F      | H      |
+--------+--------+
| E      | I      |
+--------+--------+
| H      | I      |
+--------+--------+
| E      | H      |
+--------+--------+
| I      |        |
+--------+--------+

Here E is target for A, and E is also source for F, H & I. and so on...
OUTPUT should be.. (Different unique path with highest level value) 

B->D->C->F->H->I  
A->E->F->H->I  
A->E->H->I
A->E->I

craete table 

create table table1 (source varchar2(40),target varchar2(40));

insert command is: 

insert into table1 values ('A','E');
insert into table1 values ('B','D');
insert into table1 values ('C','F');
insert into table1 values ('D','C');
insert into table1 values ('E','F');
insert into table1 values ('F','H');
insert into table1 values ('E','I');
insert into table1 values ('H','I');
insert into table1 values ('E','H');
insert into table1 (SOURCE) values ('I');

My executed query (which is not giving proper result ) is

select path from (select CONNECT_BY_ISLEAF leaf,
SYS_CONNECT_BY_PATH(source,'->') path from table1 where leaf=1;

Please provide the solution using SQL or PLSQL.
Thank You..

Upvotes: 0

Views: 69

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44961

select          CONNECT_BY_ROOT source || SYS_CONNECT_BY_PATH(target,'->') as path

from            table1 t

where           connect_by_isleaf = 1        

connect by      source = prior target    
            and target is not null

start with      source in 
                (
                    select source from table1 
                    minus 
                    select target from table1 
                )
;            

+------------------+
| PATH             |
+------------------+
| A->E->F->H->I    |
+------------------+
| A->E->H->I       |
+------------------+
| A->E->I          |
+------------------+
| B->D->C->F->H->I |
+------------------+

Upvotes: 2

Related Questions