Using the EMP table as an example I want to create a query that will show a
list of employees and the mgrs above them. Like this:
ENAME MGRS
--------------- ----------
SMITH SMITH
SMITH FORD
SMITH JONES
SMITH KING
ALLEN ALLEN
ALLEN BLAKE
ALLEN KING
WARD WARD
WARD BLAKE
WARD KING
JONES JONES
JONES KING
MARTIN MARTIN
MARTIN BLAKE
MARTIN KING
BLAKE BLAKE
BLAKE KING
CLARK CLARK
CLARK KING
SCOTT SCOTT
SCOTT JONES
SCOTT KING
KING KING
TURNER TURNER
TURNER BLAKE
TURNER KING
ADAMS ADAMS
ADAMS SCOTT
ADAMS JONES
ADAMS KING
JAMES JAMES
JAMES BLAKE
JAMES KING
FORD FORD
FORD JONES
FORD KING
MILLER MILLER
MILLER CLARK
MILLER KING
So far I've got it to this:
1 select lpad(' ',3*level-3)||ename org_char, leve
2 empno, mgr
3 from emp
4* connect by prior mgr = empno
ORG_CHAR LEVEL EMPNO MGR
--------------- ---------- ---------- ----------
SMITH 1 7369 7902
FORD 2 7902 7566
JONES 3 7566 7839
KING 4 7839
ALLEN 1 7499 7698
BLAKE 2 7698 7839
KING 3 7839
WARD 1 7521 7698
BLAKE 2 7698 7839
KING 3 7839
JONES 1 7566 7839
KING 2 7839
MARTIN 1 7654 7698
BLAKE 2 7698 7839
KING 3 7839
BLAKE 1 7698 7839
KING 2 7839
CLARK 1 7782 7839
KING 2 7839
SCOTT 1 7788 7566
JONES 2 7566 7839
KING 3 7839
KING 1 7839
TURNER 1 7844 7698
BLAKE 2 7698 7839
KING 3 7839
ADAMS 1 7876 7788
SCOTT 2 7788 7566
JONES 3 7566 7839
KING 4 7839
JAMES 1 7900 7698
BLAKE 2 7698 7839
KING 3 7839
FORD 1 7902 7566
JONES 2 7566 7839
KING 3 7839
MILLER 1 7934 7782
CLARK 2 7782 7839
KING 3 7839
39 rows selected.
Which brain cell am I missing today that will help me get what I want?
Thanks,
Jeff Eberhard
Database Administrator
Rolls-Royce Gear Systems
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Eberhard, Jeff
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
