Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2012
    Posts
    5

    Retriving Multiple Rows based on single Value

    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)

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,006
    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>

  3. #3
    Join Date
    Sep 2012
    Posts
    5
    Thanks for ur reply. but i want the Manager of BLAKE also to be displayed in the format
    EMPNO ENAME SupervisorID

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,006
    What's preventing you?

  5. #5
    Join Date
    Sep 2012
    Posts
    5
    Thanks Littlefoot for your help.

  6. #6
    Join Date
    Jun 2004
    Posts
    746
    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.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •