If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Retriving Multiple Rows based on single Value

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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)
Reply With Quote
  #2 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,962
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>
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,962
What's preventing you?
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Sep 2012
Posts: 5
Thanks Littlefoot for your help.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jun 2004
Posts: 727
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On