Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL: recursive select
A copy of this was sent to Harald Mitterhofer <Harald.Mitterhofer_at_siemens.at>
(if that email address didn't require changing)
On Mon, 20 Dec 1999 17:27:50 +0100, you wrote:
>hi!
>
>i have a general question concerning self-referencing tables;
>let's assume a table of employees, where each employee's manager is
>stored in the same table and managers can themselve have managers;
>
>is there a way to query all managers of person X with ONLY ONE
>SELECT-statement?
>
>e.g.: X has manager Y, who could possibly have manager Z, who
>could...(unknown depth of recursion!)
>=> managers of X are Y,Z,...;
>
>/thanxs
SQL> select empno, ename, mgr from emp
2 start with empno = 7788
3 connect by prior mgr = empno;
EMPNO ENAME MGR
---------- ---------- ----------
7788 SCOTT 7566 7566 JONES 7839 7839 KING
Scott works for Jones works for King....
--
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 Mon Dec 20 1999 - 11:31:30 CST