SQL query to get the employee name and their manager name from the same table

user_2000446 0
Viewed 49336

Employee table 

Employee_id  Employee_name   Manager_id
-------------------------------------
Emp00001     Ram             Emp00005
Emp00002     Sharath         Emp00003
Emp00003     Nivas           Emp00005
Emp00004     Praveen         Emp00002
Emp00005     Maharaj         Emp00002

Output 

Employee Name    Manager Name
------------------------------
Ram              Maharaj
Sharath          Nivas
Nivas            Maharaj
Praveen          Sharath
Maharaj          Sharath

In the employee table, there are three columns Employee_id, employee_name and manager_id. From the table, how to fetch the employee name and their manager name?

select sql-server sql
8 Answers

You can self-join the table to get the manager's name from his ID:

SELECT e.employee_name, m.employee_name AS manager_name
FROM   employee e
JOIN   employee m on e.manager_id = m.employee_id
user_24227760

Please try this

SELECT employee_name AS Employee_Name,(SELECT employee_name FROM   employee  where
employeeid=ManagerID ) AS Manager_Name  FROM   employee
user_60601680

enter image description here

Using below query you can get Employeename and ManagerName here i have only one table EmpMgr: select e.employeename as ename ,e.managerid as mgrid , e1.employeename as managername from EmpMgr e join EmpMgr e1 on e.managerid=e1.employeeid

Note : you can get all the employees name irrespective of manager name using the left join

select e.employeename as ename ,e.managerid as mgrid , e1.employeename as managername from EmpMgr e left join EmpMgr e1 on e.managerid=e1.employeeid

user_64225280

Select * from employee

select e1.empname,e2.empname  as managername,e1.salary,
e1.mrg,e1.empno,e1.job,e2.mrg as BossMRG from employee e1
join
employee e2 on e1.mrg=e2.empno
left join 
employee e3 on e1.mrg=e3.empno and e3.job='manager' or e3.empno=e2.mrg

enter image description here

user_138664140

Required table :- Employee.

Query :-

SELECT e.Employee Name,
       e.Employee Name as Manager Name
  FROM Employee e JOIN Employee m 
    ON e.Employee id = m.Manager id

Explanation :-

By giving this condition "Employee e JOIN Employee m" it would automatically consider single table as two different table as "e" and "m" and then compare Employee id from table e to the manager id of table m. whenever it find match that both the ID's are same that will get added to the result.

user_75204550

Hello Friends, Please check below solution.

This is result screenshot: enter image description here

This is the query:

SELECT e.employee_name,m.Employee_name FROM   EmpTable e 
    INNER JOIN EmpTable m 
            ON M.Employee_id =e.manager_id
user_119547820

Hopefully, manager table will be available in your DB

SELECT employee_name, M.manager_name
FROM   employee e
INNER JOIN tableManager M ON e.ManagerID = M.MangerID
user_15688060 
SELECT e.ename,m.ename FROM   Emp e 
    INNER JOIN Emp m 
            ON M.EMPNO =e.mgr
user_191982160
Related