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

User · Answer

Select e lastname as employee  m lastname as manager   from employees e employees m  where e managerid m employyid

User · Answer

question - DISPLAY EMPLOYEE NAME   HIS DATE OF JOINING  HIS MANAGER NAME  amp  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server