How to get the employees with their managers

Question

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 gt  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 gt  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

User · Answer

You could have just changed your query to:

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

This would tell the engine that for the inner emp table, empno should be matched with mgr column from the outer table. enter image description here

User · Answer

Perhaps your subquery  SELECT ename FROM EMP WHERE empno   mgr  thinks  give me the employee records that are their own managers   i e   where the empno of a row is the same as the mgr of the same row    have you considered perhaps rewriting this to use an inner  self  join   I m asking  becuase 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

User · Answer

SELECT ename FROM EMP WHERE empno   mgr    There are no records in EMP that meet this criteria   You need to self-join to get this relation   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 selected will not list your president because it s an inner join  I m thinking you ll be back when you discover your output isn t what your  I suspect  homework assignment required  Here s the actual test case    gt  select   from emp    empno   ename      job      deptno   mgr   ------- ------- ----------- -------- ------   7839   king    president       10          7698   blake   manager         30   7839  2 rows    gt  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 an outer join returns all the rows  An inner join will produce the following    gt  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

User · 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

User · Answer

TRY THIS  SELECT E ename E empno ISNULL E ename  NO MANAGER   AS MANAGER FROM emp e INNER JOIN emp M ON  M empno E empno   Instaed of subquery use self join

[sql] How to get the employees with their managers

Examples related to sql

Examples related to oracle

Examples related to sqlplus