Re: Recursive select

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 18 Nov 1999 11:45:37 -0500
Message-ID: <uyw0OEb695aC0qQ+1fWkbKE0ibLi@4ax.com>

A copy of this was sent to buurd_at_my-deja.com (if that email address didn't require changing) On Thu, 18 Nov 1999 15:39:59 GMT, you wrote:

> Hi... i want to ask a question like this:
>List all ancesters from person p. No only the closest one but ALL that
>is in the table. My table looks (somewhat simplified) like this
>
>table related(
> id int,
> name varchar2(30),
> parent int
>)
>
>Tia

look at the connect by clause:

  1 select ename, empno, mgr

  2          from emp
  3        start with ename = 'SMITH'
  4*      connect by prior mgr = empno

scott_at_8.0> /

ENAME EMPNO MGR
---------- ---------- ----------

SMITH            7369       7902
FORD             7902       7566
JONES            7566       7839
KING             7839

here SMITH works for FORD works for JONES works for KING. we started with smith and then took smiths MGR field and found the matching EMPNO (ford's record). it walks the tree for us.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Nov 18 1999 - 10:45:37 CST