Given an employee and manager table, write a SQL syntax that could be used to find out an employee's manager's manager, assuming all managers are in the employee table?
select emp_manager from emp where emp_name = (select emp_manager from emp where emp_name ='XYZ');considering we are searching by employee name in case of emp id we can do the same
it is assumed that u have created a single table for populating data of Employee and Manager (.a Manager is also an Employee so s/he will be residing in the same table) ..
The sample data would be like this
EmployeeID ----- EmployeeName ------- ManagerID
1 Akhtar 0
2 Bilal 1
3 Faheem 2
** This query will return "Akhtar" for given EmployeeID = 3.
SELECT m.EmployeeName FROM #EmpTemp m WHERE m.EmloyeeID = (SELECT e.ManagerID FROM #EmpTemp e WHERE e.EmloyeeID = (Select t.ManagerID FROM #EmpTemp t WHERE t.EmloyeeID = 3))
** This is solution is workable in case of 3-levels only not even to 2-levels of hierarchy. An n-level solution can be achieve by writing a Recursive stored procedure and that will also be given soon.
biswa
Jul 11th, 2006
SELECT worker.ENAME || ' works for ' 2 || manager.ENAME 3 FROM emp worker, emp manager 4 WHERE worker.mgr = manager.empno ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO GRADE ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- 7900 JAMES CLERK 7698 03-DEC-81 950 30 1 7902 FORD ANALYST 7566 03-DEC-81 3000 20 3 7934 MILLER CLERK 7782 23-JAN-82 1300 10 1 7982 MESSI MANAGER 7839 28-APR-87 2800 600 40
output is
WORKER.ENAME||'WORKSFOR'||MANAG ------------------------------- SCOTT works for JONES FORD works for JONES ALLEN works for BLAKE WARD works for BLAKE JAMES works for BLAKE TURNER works for BLAKE MARTIN works for BLAKE MILLER works for CLARK ADAMS works for SCOTT JONES works for KING CLARK works for KING
WORKER.ENAME||'WORKSFOR'||MANAG ------------------------------- MESSI works for KING BLAKE works for KING SMITH works for FORD
amy
Mar 20th, 2007
select e1.name, e2.name, e3.name from employee e1 left join employee e2 on e1.manager_id = e2.employee_id left join employee e3 on e2.manager_id = e3.employee_id
Given an employee and manager table, write a SQL syntax that could be used to find out an employee's manager's manager, assuming all managers are in the employee table?