user770022 user770022 - 4 months ago 13
SQL Question

How to get the employees with their managers

This is what I want the output to look like:

Employee Emp# Manager Mgr#
BLAKE 7698 KING 7839
CLARK 7782 KING 7839
JONES 7566 KING 7839
MARTIN 7654 BLAKE 7698
ALLEN 7499 BLAKE 7698
TURNER 7844 BLAKE 7698
JAMES 7900 BLAKE 7698
WARD 7521 BLAKE 7698
FORD 7902 JONES 7566
SMITH 7369 FORD 7902
SCOTT 7788 JONES 7566
ADAMS 7876 SCOTT 7788
MILLER 7934 CLARK 7782


Here's what I got:

SQL> SELECT ename, empno, (SELECT ename FROM EMP WHERE empno = mgr)AS MANAGER, mgr from emp order by empno;

ENAME EMPNO MANAGER MGR
---------- ---------- ---------- ----------
SMITH 7369 7902
ALLEN 7499 7698
WARD 7521 7698
JONES 7566 7839
MARTIN 7654 7698
BLAKE 7698 7839
CLARK 7782 7839
SCOTT 7788 7566
KING 7839
TURNER 7844 7698
ADAMS 7876 7788

ENAME EMPNO MANAGER MGR
---------- ---------- ---------- ----------
JAMES 7900 7698
FORD 7902 7566
MILLER 7934 7782


I can't find why the manager field is blank.

Here's the table:

SQL> select empno, ename, job,deptno, mgr from emp;

EMPNO ENAME JOB DEPTNO MGR
---------- ---------- --------- ---------- ----------
7839 KING PRESIDENT 10
7698 BLAKE MANAGER 30 7839
7782 CLARK MANAGER 10 7839
7566 JONES MANAGER 20 7839
7654 MARTIN SALESMAN 30 7698
7499 ALLEN SALESMAN 30 7698
7844 TURNER SALESMAN 30 7698
7900 JAMES CLERK 30 7698
7521 WARD SALESMAN 30 7698
7902 FORD ANALYST 20 7566
7369 SMITH CLERK 20 7902

EMPNO ENAME JOB DEPTNO MGR
---------- ---------- --------- ---------- ----------
7788 SCOTT ANALYST 20 7566
7876 ADAMS CLERK 20 7788
7934 MILLER CLERK 10 7782


14 rows selected.

Answer

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