How to get employees with your managers

Question

This is what I want the result to look like this:

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 is 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 not find why the manager field is empty.

Here is 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 1

This is a classic standalone association, 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 a president who does not have a manager, use the outer join in Oracle syntax instead of the inner join:

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

Or in the 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 

Answer 2

 (SELECT ename FROM EMP WHERE empno = mgr) 

There are no records in EMP that meet these criteria.

You need to team up to get this attitude.

 SELECT e.ename AS Employee, e.empno, m.ename AS Manager, m.empno FROM EMP AS e LEFT OUTER JOIN EMP AS m ON e.mgr =m.empno; 

EDIT:

The answer you have chosen will not be listed by your president, because it is an internal connection. I think that you will return when you find out that your result does not correspond to your task (I suspect). Here is the actual test case:

 > select * from emp; empno | ename | job | deptno | mgr -------+-------+-----------+--------+------ 7839 | king | president | 10 | 7698 | blake | manager | 30 | 7839 (2 rows) > SELECT e.ename employee, e.empno, m.ename manager, m.empno FROM emp AS e LEFT OUTER JOIN emp AS m ON e.mgr =m.empno; employee | empno | manager | empno ----------+-------+---------+------- king | 7839 | | blake | 7698 | king | 7839 (2 rows) 

The difference is that the outer join returns all rows. The inner join will create the following:

 > SELECT e.ename, e.empno, m.ename as manager, e.mgr FROM emp e, emp m WHERE e.mgr = m.empno; ename | empno | manager | mgr -------+-------+---------+------ blake | 7698 | king | 7839 (1 row) 

Answer 3

Perhaps your subquery (SELECT ename FROM EMP WHERE empno = mgr) thinks, give me records of employees who are their own managers! (that is, where the empno line matches the mgr of the same line.)

Do you think it’s possible to rewrite this in order to use the inner (self) join? (I ask because I'm not even sure if the following will work or not.)

 SELECT t1.ename, t1.empno, t2.ename as MANAGER, t1.mgr from emp as t1 inner join emp t2 ON t1.mgr = t2.empno order by t1.empno; 

Answer 4

TRY IT

 SELECT E.ename,E.empno,ISNULL(E.ename,'NO MANAGER') AS MANAGER FROM emp e INNER JOIN emp M ON M.empno=E.empno 

The subquery entered uses self join

Answer 5

You could just change your request to:

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

This tells the engine that for the internal table emp empno should be mapped to the mgr column from the external table.