CODEWITHSUNDEEP
sqlsql-serverselect
Sowbarani Karthikeyan
Sowbarani Karthikeyan

Reputation: 161

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

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?

Upvotes: 4

Views: 77484

Answers (8)

Surya
Surya

Reputation: 1

SELECT e.ename,m.ename FROM   Emp e 
    INNER JOIN Emp m 
            ON M.EMPNO =e.mgr

Upvotes: -2

Ajeet Verma
Ajeet Verma

Reputation: 1123

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

Upvotes: 0

Ayam
Ayam

Reputation: 41

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

Upvotes: 1

Gaurav Joshi
Gaurav Joshi

Reputation: 1001

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

Upvotes: 1

Siddharth Jain
Siddharth Jain

Reputation: 1

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.

Upvotes: 0

Manish Singh
Manish Singh

Reputation: 1014

Please try this

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

Upvotes: 1

A.Goutam
A.Goutam

Reputation: 3494

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

Upvotes: -1

Mureinik
Mureinik

Reputation: 311796

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

Upvotes: 12

Related Questions