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

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,'22-Feb-12',950,null,30);
insert into EMPLOYEES values (7521,'Ward','Salesman',7698,'22-Feb-12',1250,500,30);
insert into EMPLOYEES values (7902,'Ford','Analyst',7566,'03-Dec-11',3000,null,20);
insert into EMPLOYEES values (7369,'Smith','Clerk',7902,'17-Dec-10',800,null,20);
insert into EMPLOYEES values (7788,'Scott','Analyst',7566,'09-Dec-12',3000,null,20);
insert into EMPLOYEES values (7876,'Adams','Clerk',7788,'12-Jan-10',1100,null,20);
insert into EMPLOYEES values (7934,'Miller','Clerk',7782,'23-Jan-12',1300,null,10);

The following is my SELECT script:

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

Im getting the employees with their corresponding manager's ID;

Ford    7566
Scott   7566
Allen   7698
James   7698
Martin  7698
Turner  7698
Ward    7698
Miller  7782
Adams   7788
Blake   7839
Clark   7839
Jones   7839
Smith   7902

How do I list the manager name as well? *Am I doing the right inner join?*

There are three tables- Equities(coulmns: ID,ISIN) and Bond(coulmns: ID,ISIN). Third table Securities(coulmns: ID,ISIN) contains all data from Equities and Bond tables. Write SQL queries to validate below: (1) Securities table should contain all the data from Equities and Bonds tables. (2) Securities table should not contain any data other than present in Equities and Bonds tables

Select e.lastname as employee ,m.lastname as manager
  from employees e,employees m
 where e.managerid=m.employyid(+)

Your query is close you need to join using the mgr and the empid

on e1.mgr = e2.empid

So the full query is:

select e1.ename Emp,
  e2.eName Mgr
from employees e1
inner join employees e2
  on e1.mgr = e2.empid

See SQL Fiddle with Demo

If you want to return all rows including those without a manager then you would change it to a LEFT JOIN (for example the president):

select e1.ename Emp,
  e2.eName Mgr
from employees e1
left join employees e2
  on e1.mgr = e2.empid

See SQL Fiddle with Demo

The president in your sample data will return a null value for the manager because they do not have a manager.

No, the correct join is:

inner join Employees m on e.mgr = m.EmpID;

You need to match the ManagerID for the current employee with the EmployeeID of the manager. Not with the ManagerID of the manager.

update
As noted by Andrey Gordeev:
You'd also need to add m.Ename to your SELECT query in order to get the name of the Manager in your result. Otherwise you'd only get the managerID.

You have an incorrect ON clause at the join, this works:

inner join Employees m on e.mgr = m.EmpId;

The mgr column references the EmpId column.

DEMO

SELECT DISTINCT e.Ename AS Employee, 
    m.mgr AS reports_to, 
    m.Ename AS manager 
FROM Employees e, Employees m 
WHERE e.mgr=m.EmpID;

question:-.DISPLAY EMPLOYEE NAME , HIS DATE OF JOINING, HIS MANAGER NAME & HIS MANAGER'S DATE OF JOINING. ANS:- select e1.ename Emp,e1.hiredate, e2.eName Mgr,e2.hiredate from emp e1, emp e2 where e1.mgr = e2.empno

select e.ename as Employee, m.ename as Manager
from emp e, emp m
where e.mgr = m.empno

If you want to get the result for all the records (irrespective of whether they report to anyone or not), append (+) on the second table's name

select e.ename as Employee, m.ename as Manager
from emp e, emp m
where e.mgr = m.empno(+)

select a.empno,a.ename,a.job,a.mgr,B.empno,B.ename as MGR_name, B.job as MGR_JOB from 
    emp a, emp B where a.mgr=B.empno ;