OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » What is Query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

What is Query [message #20803]

Fri, 21 June 2002 03:14

Laxman Chepuri
Messages: 8
Registered: May 2002

Junior Member

I have one table Employee with two fields empid mgrid

empid mgrid
EMP1
EMP2 EMP1
EMP3 EMP1
EMP4 EMP3
EMP5 EMP2
EMP6 EMP5
EMP7
EMP8 EMP4
EMP9

Now I want to retrieve a particular employee superiors list

for ex:

EMP4 superiors

EMP3
EMP1
EMP7
EMP9

Actually EMP3,EMP1 are superiors But I want to display
EMP7 and EMP9 also because MgrId is not assigned to them

How can i write a query

My Thanks to Anisha

Report message to a moderator

Re: What is Query [message #20804 is a reply to message #20803]

Fri, 21 June 2002 03:29

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

the regular connect by clause should help you.
something like this

SQL> select empno,ename,mgr from emp;

     EMPNO ENAME                                 MGR
---------- ------------------------------ ----------
      7369 SMITH                                7902
      7499 ALLEN                                7698
      7521 WARD                                 7698
      7566 JONES                                7839
      7654 MARTIN                               7698
      7698 BLAKE                                7839
      7782 CLARK
      7788 SCOTT                                7566
      7839 KING
      7844 TURNER                               7698
      7876 ADAMS                                7788
      7900 JAMES                                7698
      7902 FORD                                 7566
      7934 MILLER

14 rows selected.

SQL> select job,lpad(' ',2*(level-1))||ename ename
  2   from emp e
  3   connect by prior empno = mgr
  4   start with mgr is null;

JOB       ENAME
--------- ------------------------------
MANAGER   CLARK
PRESIDENT KING
MANAGER     JONES
ANALYST       SCOTT
CLERK           ADAMS
ANALYST       FORD
CLERK           SMITH
MANAGER     BLAKE
SALESMAN      ALLEN
SALESMAN      WARD
SALESMAN      MARTIN
SALESMAN      TURNER
CLERK         JAMES
CLERK     MILLER

14 rows selected.

Report message to a moderator

Previous Topic:	How can I reference a Composite key??
Next Topic:	Re: How do I set the value of a Composite Foreign Key ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 09:53:43 CDT 2024