Re: How to chain through tables.

It sounds to me like you want to do a Hierarchical Query, the format would be something like:

select col_name, col_name, ...
from table_name
connect by prior col_name = col_name
start with col_name;

the classic example would be for an employee table where employees have a manager id in the row:

select empno, ename, job, mgr from emp
connect by prior empno = mgr
start with mgr is nul

in the connect by prior part of it, empno is the child and mgr is the parent

start with mgr is null tells oracle where to begin traversing the tree...

On Fri, 11 Dec 1998 21:00:40 -0500, roy_at_popmail.med.nyu.edu (Roy Smith) wrote:

>I've got a table, call it foo, which looks sort of like this:
>
>id name parent
>1 foo 2
>2 bar 4
>3 other 5
>4 baz NULL
>5 stuff NULL
>6 xyz 2
>
>Each row in the table is essentially a node in a tree. I want to get all
>the rows which trace the chain from a given row, through the parent
>pointers, to the root, i.e. starting with 1, I want to get back rows 1, 2,
>and 4. Starting with 6, I want to get 6, 2, and 4. And so on. Is there
>a single SQL statement I can write to execute that query?
Received on Sat Dec 12 1998 - 13:47:06 CST