Traversing, while keeping a constant

[Ray]

I have a real puzzle, I figured someone on here could help. I have a
table that tracks all parents and children. I would like to set
iterate over all entities where I set a variable to equal a root
parent and return all relations disregarding why the relation exists.
Essentially here is what I would like to do however, this won't work
for obvious reasons:

select parent, (select child from table
start with parent = (select parent from table)
connect by parent = prior child)
from table

so instead of
1,2
1,3
3,4
3,5

I need my result to be used in a view where
1,2
1,3
1,4
1,5
3,4
3,5

Anyone know of a way to do this? I know that I can use a cursor and
keep track of the root parent while using a cursor to track all
relations for the root parent and place that into a function, but this
will be constantly growing and I can't process this logic everytime
there is an update.
Thanks for any insight...
Ray

[VC]

Hello Ray,

Apparently you want to get the transitive closure over your tree.

Given :

create table t1(PARENT INT, CHILD INT);

insert into t1 values(null, 1);
insert into t1 values(1, 2);
insert into t1 values(1, 3);
insert into t1 values(2, 4);
insert into t1 values(2, 5);
insert into t1 values(3, 6);
insert into t1 values(3, 7);
insert into t1 values(5, 8);

In Oracle 9i., one way would be:

select
substr(path,2,instr(path,'/',1,2)-2) parent,
substr(path, instr(path,'/',-1,1)+1, length(path)-instr(path,'/',-1,1))
child,
distance
from (select sys_connect_by_path(child,'/') path, level-1 distance
from t1
connect by prior child=parent)
where instr(path,'/',1,2)!= 0

.... and another:

select p.child parent ,
c.child child,
level-1 distance
from t1 p, t1 c
where level > 1
connect by prior c.child = c.parent and prior p.child=p.child
start with p.child= c.child

PARENT CHILD DISTANCE
2 4 1
2 5 1
2 8 2
3 6 1
3 7 1
5 8 1
1 2 1
1 4 2
1 5 2
1 8 3
1 3 1
1 6 2
1 7 2

Both queries are not very efficient for large trees. Which one is worse is
left as an exercise for the reader ;)

In Oracle 8i, one has to write a stored procedure to perform BFS or DFS.
The stored procedure solution will be more efficient in 9i too since only
one tree traversal is needed.

Rgds.
"Ray" <rb*******@hotmail.com> wrote in message
news:cb**************************@posting.google.c om...

I have a real puzzle, I figured someone on here could help. I have a
table that tracks all parents and children. I would like to set
iterate over all entities where I set a variable to equal a root
parent and return all relations disregarding why the relation exists.
Essentially here is what I would like to do however, this won't work
for obvious reasons:

select parent, (select child from table
start with parent = (select parent from table)
connect by parent = prior child)
from table

so instead of
1,2
1,3
3,4
3,5

I need my result to be used in a view where
1,2
1,3
1,4
1,5
3,4
3,5

Anyone know of a way to do this? I know that I can use a cursor and
keep track of the root parent while using a cursor to track all
relations for the root parent and place that into a function, but this
will be constantly growing and I can't process this logic everytime
there is an update.
Thanks for any insight...
Ray