Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Recursive query: START WITH and CONNECT BY

user3873926Oct 15 2010 — edited Oct 19 2010
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

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 16 2010
Added on Oct 15 2010
14 comments
11,436 views