Home » SQL & PL/SQL » SQL & PL/SQL » Self Join to fetch required data (Oracle 9i)
Self Join to fetch required data [message #427815] Mon, 26 October 2009 00:16 Go to next message
RaniIyer
Messages: 3
Registered: October 2009
Location: Mumbai
Junior Member
Hi,

I am new to oracle. Please help me in this. I am trying to write a query which will give me employee details as well as their Manager's details.

CREATE TABLE EMP1
(
  EMPNO  NUMBER(4),
  ENAME  VARCHAR2(10 BYTE),
  JOB    VARCHAR2(9 BYTE),
  MGR    NUMBER(4)
)

INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566); 
INSERT INTO EMP1 ( EMPNO, ENAME, JOB, MGR ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782); 



     EMPNO ENAME      JOB              MGR
---------- ---------- --------- ----------
      7369 SMITH      CLERK           7902
      7499 ALLEN      SALESMAN        7698
      7521 WARD       SALESMAN        7698
      7566 JONES      MANAGER         7839
      7654 MARTIN     SALESMAN        7698
      7698 BLAKE      MANAGER         7839
      7782 CLARK      MANAGER         7839
      7788 SCOTT      ANALYST         7566
      7839 KING       PRESIDENT
      7844 TURNER     SALESMAN        7698
      7876 ADAMS      CLERK           7788

     EMPNO ENAME      JOB              MGR
---------- ---------- --------- ----------
      7900 JAMES      CLERK           7698
      7902 FORD       ANALYST         7566
      7934 MILLER     CLERK           7782

14 rows selected.


The above is the tablescript along with the insert scripts.
The requirement is, when I pass employee numbers, I should be able to get employee details along with their manager details.
The output should be as follows:
When I pass EMP No as 7369,7499 and 7788

          EMPNO ENAME      JOB              MGR
---------- ---------- --------- ----------
      7369 SMITH      CLERK           7902
      7499 ALLEN      SALESMAN        7698      
      7788 SCOTT      ANALYST         7566
      7698 BLAKE      MANAGER         7839
      7902 FORD       ANALYST         7566
      7566 JONES      MANAGER         7839
      7839 KING       PRESIDENT


I have tried to write the query as follows:

SELECT a.EMPNO, a.ENAME,a.JOB,a.MGR
FROM EMP1 a, EMP1 b
WHERE a.EMPNO = b.MGR
and a.empno in (7369,7499,7788); 



But the output is coming as follows:
     EMPNO ENAME      JOB              MGR
---------- ---------- --------- ----------
      7788 SCOTT      ANALYST         7566


Regards,
Rani Iyer
Re: Self Join to fetch required data [message #427819 is a reply to message #427815] Mon, 26 October 2009 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Make a query that returns the data of the employee;
Make a query that returns the data of their mgr;
Union both.

Regards
Michel
Re: Self Join to fetch required data [message #427822 is a reply to message #427819] Mon, 26 October 2009 00:52 Go to previous messageGo to next message
RaniIyer
Messages: 3
Registered: October 2009
Location: Mumbai
Junior Member
Hi Michel,

Thanks for you immediate reply.

As per your suggestion, I wrote the query as follows:
SELECT a.EMPNO, a.ENAME,a.JOB,a.MGR
FROM EMP1 a
WHERE a.empno in (7369,7499,7788) 
UNION
SELECT b.EMPNO,b.ENAME,b.JOB,b.MGR
FROM EMP1 b
WHERE b.EMPNO in(SELECT MGR FROM EMP1 WHERE EMPNO in (7369,7499,7788));


I get the ouput as follows:

     EMPNO ENAME      JOB              MGR
---------- ---------- --------- ----------
      7369 SMITH      CLERK           7902
      7499 ALLEN      SALESMAN        7698
      7566 JONES      MANAGER         7839
      7698 BLAKE      MANAGER         7839
      7788 SCOTT      ANALYST         7566
      7902 FORD       ANALYST         7566

6 rows selected.


But this doesnot show me the details of mgr 7839.

Please help.

Regards,
Rani Iyer
Re: Self Join to fetch required data [message #427827 is a reply to message #427822] Mon, 26 October 2009 01:40 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I suspect that you are, actually, looking for a hierarchical query. Something like this:
SQL> select distinct e.empno, e.ename, e.job, e.mgr, m.ename mgr_name
  2  from emp1 e, emp1 m
  3  where e.mgr = m.empno (+)
  4  connect by nocycle prior e.mgr = e.empno
  5  start with e.empno in (7369, 7499, 7788);

     EMPNO ENAME      JOB              MGR MGR_NAME
---------- ---------- --------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 BLAKE
      7369 SMITH      CLERK           7902 FORD
      7902 FORD       ANALYST         7566 JONES
      7839 KING       PRESIDENT
      7698 BLAKE      MANAGER         7839 KING
      7788 SCOTT      ANALYST         7566 JONES
      7566 JONES      MANAGER         7839 KING

7 rows selected.

SQL>
Or, slightly different (and, perhaps, easier to understand)
SQL> select lpad(e.ename,Length(e.ename) + LEVEL * 3 - 3,' ') rb,
  2         e.empno, e.job, e.mgr, m.ename mgr_name
  3  from emp1 e, emp1 m
  4  where e.mgr = m.empno (+)
  5  connect by nocycle prior e.mgr = e.empno
  6  start with e.empno in (7369, 7499, 7788);

RB                        EMPNO JOB              MGR MGR_NAME
-------------------- ---------- --------- ---------- ----------
SMITH                      7369 CLERK           7902 FORD
   FORD                    7902 ANALYST         7566 JONES
      JONES                7566 MANAGER         7839 KING
         KING              7839 PRESIDENT
ALLEN                      7499 SALESMAN        7698 BLAKE
   BLAKE                   7698 MANAGER         7839 KING
      KING                 7839 PRESIDENT
SCOTT                      7788 ANALYST         7566 JONES
   JONES                   7566 MANAGER         7839 KING
      KING                 7839 PRESIDENT

10 rows selected.

SQL>
Re: Self Join to fetch required data [message #427828 is a reply to message #427822] Mon, 26 October 2009 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But this doesnot show me the details of mgr 7839.

Because it is not a DIRECT manager of the ones you mentioned.

Regards
Michel
Re: Self Join to fetch required data [message #427849 is a reply to message #427827] Mon, 26 October 2009 03:37 Go to previous message
RaniIyer
Messages: 3
Registered: October 2009
Location: Mumbai
Junior Member
Hi,

Thank you all for helping me. This has solved my problem.

Regards,

Rani Iyer
Previous Topic: ORA-06532: Subscript outside of limit
Next Topic: Shows null on update & Update not work for select statement (merged)
Goto Forum:
  


Current Time: Sat Dec 03 12:29:40 CST 2016

Total time taken to generate the page: 0.06908 seconds