Retriving Multiple Rows based on single Value

jatink · #1 (**permalink**) 11-05-12, 03:08

Hi,
i am new to Oracle. i have the following problem. i have write the Query that retrieve all the employee based on Single employeeID, but i couldn't able to retrieve SupervisorID

There are two tables say A and B. A table has the fields employeeID, SupervisorID etc and B table has the field employeeID (Don't have SupervisorID).
Based on this when i supplied Single employeeID,i want to retrieve its peers and their manager.

i.e when i suppleid 101 as employeeID then i want 101,102,103,104,105(peer) and 111(Manager)

Littlefoot · #2 (**permalink**) 11-05-12, 06:24

Here's an example (based on Scott's schema). Employees, sorted by MGR (manager):

Code:

SQL> select mgr, empno, ename
  2  from emp
  3  order by mgr, empno;

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

14 rows selected.

SQL>

Now, suppose that you enter EMPNO = 7844 as a parameter. That is TURNER. His manager is 7698 (BLAKE). So, as a result, you want to fetch all emplyoees whose manager is BLAKE, as well as BLAKE himself.

OK, here's one option: first, find the manager and then use that value in order to retrieve desired records:

Code:

SQL> with manager as
  2    (select mgr
  3     from emp
  4     where empno = &supplied_empno
  5    )
  6  select e.empno, e.ename
  7  from emp e, manager m
  8  where e.mgr = m.mgr     --> employees whose manager is BLAKE
  9     or e.empno = m.mgr   --> this is BLAKE
 10  ;
Enter value for supplied_empno: 7844

     EMPNO ENAME
---------- ----------
      7499 ALLEN
      7521 WARD
      7654 MARTIN
      7698 BLAKE
      7844 TURNER
      7900 JAMES

6 rows selected.

SQL>

jatink · #3 (**permalink**) 11-05-12, 08:03

Thanks for ur reply. but i want the Manager of BLAKE also to be displayed in the format
EMPNO ENAME SupervisorID

Littlefoot · #4 (**permalink**) 11-05-12, 12:40

What's preventing you?

jatink · #5 (**permalink**) 11-06-12, 00:46

Thanks Littlefoot for your help.

cis_groupie · #6 (**permalink**) 11-06-12, 05:34

jatink, look at the example that Littlefoot has kindly provided for you, then think about how you can change it so that it produces the result that you want.

Internet Services

Web Development

Digital Marketing

Consumer Tech