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?*
List all employee's names and their managers by manager name using an inner join.
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;
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With