Find manager for employee

ID employee department manager
----------------------------------------------
1 Suresh c++ NULL
2 Suresh c++ NUll
3 Suresh c++ 2
5 Sarathy testing 2
6 Rajaraman c# 1
7 joe Flash 1


I have the employee detail table ,In that i want to find the manager for each employee .
The employee name and manager column are present in same table

Questions by rajaramanv

Editorial / Best Answer

mktiwary  

  • Member Since Jun-2008 | Jun 6th, 2008


If you take the normal Oracle Employee (emp) table which consists of columns such as Employee Number (EMPNO) and Manager Number (MGR) then the query to find managers for all the employee is:

select e.ename as Employee, m.ename as Manager from emp e, emp m
where e.MGR = m.EMPNO

Showing Answers 1 - 9 of 9 Answers

mktiwary

  • Jun 6th, 2008
 

If you take the normal Oracle Employee (emp) table which consists of columns such as Employee Number (EMPNO) and Manager Number (MGR) then the query to find managers for all the employee is:

select e.ename as Employee, m.ename as Manager from emp e, emp m
where e.MGR = m.EMPNO

we can find manager for employee in the same table by using self join.

Ex:emp_id   emp_name   manager_id
       1          Smith            
       2          Rahul              1
       3          Sachin             2

Select e.emp_id,e.emp_name,m.last_name from
employees e, employees m
where e.manager_id = m.employee_id;

The code below retrieves the manager name. Note that employees who do not have manger is also listed.
Select
Emp1.Employee_Id,
Emp1.first_name,
Emp1.last_name,
Emp1.Manager_id,
Emp2.first_name as "Manager_First",
Emp2.last_name as "Manager_Last"
from
employees emp1, employees emp2
where
emp1.manager_id = emp2.employee_id(+)
order by emp2.employee_id

  Was this answer useful?  Yes

rohitosu

  • Jul 31st, 2012
 

Code
  1. SELECT a.employee_id, a.manager_id, A.FIRST_NAME Employee_Name, B.FIRST_NAME Manager_Name

  2.     FROM employees a

  3.     INNER JOIN employees b

  4.     ON A.MANAGER_ID = B.EMPLOYEE_ID

  5.     ORDER BY A.EMPLOYEE_ID ;

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions