From: boston103@hotmail.com   
      
   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"  wrote in message   
   news:cb2954f2.0311131242.3b3275fd@posting.google.com...   
   > 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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)