CODEWITHSUNDEEP
sqlsql-server-2008select
Im Khan
Im Khan

Reputation: 71

how to get manager's name from employ name

How do I find manager name here. Each manager is also an employee.

EmpID  Name  ManagerId
  1     A       3
  2     B       1
  3     C       2
  4     D       5
  5     E       1

Expected result is like this

EmpID  Name  ManagerName
  1     A       C
  2     B       A
  3     C       B
  4     D       E
  5     E       A

Upvotes: 3

Views: 95

Answers (3)

Mureinik
Mureinik

Reputation: 311808

You can self join the table, and use the joined instance to get the manager's name:

SELECT e.empid, e.name, m.name
FROM   employee e
JOIN   employee m on e.managerid = m.empid

Upvotes: 1

PowerStar
PowerStar

Reputation: 895

CREATE TABLE #MYTEMP
(
     EmpID  INT
    ,Name  VARCHAR(10)
    ,ManagerId INT
)

INSERT INTO #MYTEMP VALUES
(1,'A',3),( 2,'B',1),(3,'C',2),(4,'D',5),( 5,'E',1)

SELECT EMP.EmpID AS EMPNO,
       EMP.Name AS EMPLOYEE,
       MGR.Name AS MANAGER
FROM #MYTEMP EMP
JOIN #MYTEMP MGR
ON EMP.ManagerId = MGR.EmpID

Upvotes: 0

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

You have to use Left Join for the desired output..otherwise you will miss some employee who doesn't have managers .. 

SELECT e.empid, e.name Employee, ISNULL(e1.name,'') Manager
FROM   employee e
LEFT JOIN   employee e1 on e.managerid = e1.empid

Upvotes: 1

Related Questions