List all employee's names and their managers by manager name using an inner join

Question

The following is my CREATE TABLE script: ``` create table EMPLOYEES (EmpID char(4) unique Not null, Ename varchar(10), Job varchar(9), MGR char(4), Hiredate date, Salary decimal(7,2), Comm decimal(7,2), DeptNo char(2) not null, Primary key(EmpID), Foreign key(DeptNo) REFERENCES DEPARTMENTS(DeptNo)); ``` The following is my INSERT script: ``` insert into EMPLOYEES values (7839,'King','President',null,'17-Nov-11',5000,null,10); insert into EMPLOYEES values (7698,'Blake','Manager',7839,'01-May-11',2850,null,30); insert into EMPLOYEES values (7782,'Clark','Manager',7839,'02-Jun-11',2450,null,10); insert into EMPLOYEES values (7566,'Jones','Manager',7839,'02-Apr-11',2975,null,20); insert into EMPLOYEES values (7654,'Martin','Salesman',7698,'28-Feb-12',1250,1400,30); insert into EMPLOYEES values (7499,'Allen','Salesman',7698,'20-Feb-11',1600,300,30); insert into EMPLOYEES values (7844,'Turner','Salesman',7698,'08-Sep-11',1500,0,30); insert into EMPLOYEES values (7900,'James','Clerk',7698,'2…

Accepted Answer

Add `m.Ename` to your `SELECT` query:

```
select distinct e.Ename as Employee, m.mgr as reports_to, m.Ename as Manager
from EMPLOYEES e
inner join Employees m on e.mgr = m.EmpID;
```

List all employee's names and their managers by manager name using an inner join

Solution

Problem