Self Join to Get Employee Manager Name

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

Query for a self join for employee table

To perform a self join, you simply give the same table a different alias.
For example, in your employee table you would have a managerid - which stores the id of the manager.
Then to get the manager's name - you just self join to the employee table on managerid - using a different alias (I have used m in the example below):

For example, your table would look like this:

CREATE TABLE Employees (id INT, Name VARCHAR(20), ManagerId INT);

To get the Employee's Name and his/her Manager's Name, you would do something like this:

SELECT 
e.Name AS EmployeeName,
ISNULL(m.Name, 'No Manager') AS ManagerName
FROM employee e 
LEFT JOIN employee m on m.id = e.ManagerId

If you want to learn more about self joins - see here

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

List the manager's name and the number of employees in that manager's department

SELECT A.ENAME, COUNT(*) 
FROM EMP A 
   JOIN EMP B ON A.DEPTNO = B.DEPTNO 
WHERE A.JOB = 'MANAGER'
GROUP BY A.ENAME;

Please correct me if I'm wrong.

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;

---

Related Topics