Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL: recursive select

Re: SQL: recursive select

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 20 Dec 1999 12:31:30 -0500
Message-ID: <pups5scuioa4pua42vv208gedefu3bmhlc@4ax.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US