Sql Query to Get the Employee Name and Their Manager Name from the Same Table

How to write SQL query to get emp name and manager name which is in same table?

Don't use != with NULL! The correct syntax would be IS NOT NULL. However, use the built-in function COALESCE():

SELECT e1.EmpID, e1.EmpName, e1.ManagerID,
       ManagerName = COALESCE(e2.EmpName, 'No Manager')
FROM Emp e1 LEFT JOIN
     Emp e2
    ON e1.ManagerID = e2.EmpID;

Almost any operation on NULL returns NULL, including = and <>. Two prominent exceptions are IS NULL and IS NOT NULL.

Self Join to get employee manager name

CREATE VIEW AS
SELECT e1.emp_Id EmployeeId, e1.emp_name EmployeeName, 
       e1.emp_mgr_id ManagerId, e2.emp_name AS ManagerName
FROM   tblEmployeeDetails e1
       JOIN tblEmployeeDetails e2
       ON e1.emp_mgr_id = e2.emp_id

EDIT:
Left Join will work if emp_mgr_id is null.

CREATE VIEW AS 
SELECT e1.emp_Id EmployeeId, e1.emp_name EmployeeName,  
       e1.emp_mgr_id ManagerId, e2.emp_name AS ManagerName 
FROM   tblEmployeeDetails e1 
       LEFT JOIN tblEmployeeDetails e2 
       ON e1.emp_mgr_id = e2.emp_id

SQL query to find EmployeeName, Manager Name from a table

SELECT one.EmployeeName AS "Employee", two.EmployeeName AS "Manager"
FROM Employee AS one
INNER JOIN Employee_Manager AS temp ON one.EmployeeId = temp.EmployeeId
INNER JOIN Employee AS two ON temp.ManagerId = two.EmployeeId

Is it what you want to achieve?

Return an employee's first + lastname and their manager's first + last name

Do a self join to get the manager names.

SELECT e.employeeid, e.firstname, e.lastname,
       m.firstname managerFirstName, m.lastname managerLastName
FROM employee e
JOIN employee m ON e.reportsto = m.employeeid 
WHERE e.reportsto IS NOT NULL;

How to get name and manager name

You should use the same table twice using alias

select a.name, b.name as manager 
from my_table a 
left join  my_table b on a.manager  = b.id  

How would you display the manager name from employee table in an SQL query?

select 
  emp.ename as "Employee", 
  emp.empno as "Emp#", 
  emp.mgr as "Mgr#",
  m.ename as "Manager"
from 
 emp
  LEFT OUTER JOIN emp m ON
   emp.mgr = m.empno

Query to display Employee and Manager

I guess something as simple as this should do it

SELECT e.LastName + ' works for ' + e2.LastName
from  Employee e 
  join Department d on e.workdept = d.deptno
  join Employee e2 on d.mgrno = e2.empno

How it works:

• The join from Employee e with Department d will get you exact 1 row in Department
• Then join back from this one Department to employee e2, this will return exact 1 employee (the boss) from Employee e2
• Now you have the employee in e, and the boss in e2

I tested with this

declare @Employee table (empno char(6), lastname varchar(15), workdept char(3))
declare @Department table (deptno char(3), mgrno char(6))

insert into @Employee (empno, lastname, workdept) values 
  ('123456', 'Adamson', 'dp1'), 
  ('123457', 'Brown', 'dp1'), 
  ('123458', 'Jefferson', 'dp2'), 
  ('123459', 'Johnson', 'dp2'),
  ('123460', 'Jones', 'dp1'),
  ('123461', 'Lutz', 'dp1'),
  ('123462', 'Marino', 'dp2'),
  ('123463', 'Monteverde', 'dp2'),
  ('123464', 'Natz', 'dp3'),
  ('123465', 'Stern', 'dp1'),
  ('123466', 'Pulaski', 'dp2'),
  ('123467', 'Kwan', 'dp3')

insert into @Department (deptno, mgrno) values ('dp1', '123465'), ('dp2', '123466'), ('dp3', '123467')

SELECT e.LastName + ' works for ' + e2.LastName
from  @Employee e 
  join @Department d on e.workdept = d.deptno
  join @Employee e2 on d.mgrno = e2.empno

the result

Adamson works for Stern
Brown works for Stern
Jefferson works for Pulaski
Johnson works for Pulaski
Jones works for Stern
Lutz works for Stern
Marino works for Pulaski
Monteverde works for Pulaski
Natz works for Kwan
Stern works for Stern
Pulaski works for Pulaski
Kwan works for Kwan

EDIT

if you want to leave out the bosses, add this where clause to the query

where  e.empno not in (select mgrno from @Department)

or alternative this where clause

where  e.empno <> e2.empno

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;

---

Related Topics