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

    Unanswered: 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,088
    Provided Answers: 4
    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,088
    Provided Answers: 4
    What's preventing you?

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

  6. #6
    Join Date
    Jun 2004
    Posts
    794
    Provided Answers: 1
    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
  •