| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> SQL Query -- List of managers
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: Jeff.Eberhard_at_Rolls-RoyceGS.com
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: ListGuru_at_fatcity.com (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). Received on Mon Mar 31 2003 - 17:18:42 CST
 |
 |