Recursive query: START WITH and CONNECT BY
I have the following table:
MASTER SLAVE
A B
B C
D E
C A
A T
Z T
G A
and I'd like to find the chain starting from A. That is, I want to find : a->b, a->t, b->c, c->a, t->z, a->g , that is the following result:
A
B
C
G
T
Z
(all the "nodes" bounded with A, where each rows represent a "relation")
I found the following SQL construct , but the result is
A
B
C
G
T
select distinct MASTER
from (
SELECT MASTER, SLAVE, LEVEL FROM TABLE
start with MASTER = 'A' or SLAVE = 'A'
connect by prior SLAVE != 'A' and prior MASTER != 'A' and SLAVE = MASTER or MASTER = SLAVE
+)+
union
select distinct SLAVE
from (
SELECT MASTER, SLAVE, LEVEL FROM TABLE
start with MASTER = 'A' or SLAVE = 'A'
connect by prior SLAVE != 'A' and prior MASTER != 'A' and SLAVE = MASTER or MASTER = SLAVE
+)+
because I am not able to express the clause "PARENT.SLAVE = CHILD.SLAVE"
Any suggests ?
Thanks
Paul