How to Get the Employees with Their Managers

How to get the employees with their managers

This is a classic self-join, try the following:

SELECT e.ename, e.empno, m.ename as manager, e.mgr
FROM
    emp e, emp m
WHERE e.mgr = m.empno

And if you want to include the president which has no manager then instead of an inner join use an outer join in Oracle syntax:

SELECT e.ename, e.empno, m.ename as manager, e.mgr
FROM
    emp e, emp m
WHERE e.mgr = m.empno(+)

Or in ANSI SQL syntax:

SELECT e.ename, e.empno, m.ename as manager, e.mgr
FROM
    emp e
    LEFT OUTER JOIN emp m
        ON e.mgr = m.empno

List the manager's name and the number of employees in that manager's department

SELECT A.ENAME, COUNT(*) 
FROM EMP A 
   JOIN EMP B ON A.DEPTNO = B.DEPTNO 
WHERE A.JOB = 'MANAGER'
GROUP BY A.ENAME;

Please correct me if I'm wrong.

How to write a query with workers and their managers beside them in on query

You can self join the emp table to get the managers name

select e.ename, 
       d.name, 
       coalesce(e.sal, 0) + coalesce(e.comm, 0) as salary, 
       m.ename as manager
from emp e 
join dept as d on e.deptno = d.deptno
left join emp m on m.empno = e.mgr

---

Related Topics