Find Employees Who Are Not in the Same Department as Their Manager

Find Employees Who Are Not in the Same Department as Their Manager Query Using MS SQL Server?

To find employees who are not in the same department as their manager, you need to:

1. Join the employee table with itself to get each employee's manager information.
2. Compare the department of the employee with the department of their manager.

Assuming you have the following table:

• Employees:
  • EmployeeID
  • Name
  • DepartmentID
  • ManagerID (refers to EmployeeID of the manager)

Here’s how you can write the SQL query:

SQL Query

SELECT

    e.EmployeeID,

    e.Name AS EmployeeName,

    e.DepartmentID AS EmployeeDepartment,

    m.EmployeeID AS ManagerID,

    m.Name AS ManagerName,

    m.DepartmentID AS ManagerDepartment

FROM Employees e

JOIN Employees m

    ON e.ManagerID = m.EmployeeID

WHERE e.DepartmentID <> m.DepartmentID;

Explanation:

1. Self-Join:

   • JOIN Employees m ON e.ManagerID = m.EmployeeID: Joins the Employees table with itself. Here, e represents the employee and m represents the manager. This join connects each employee to their respective manager.

2. Filter:

   • WHERE e.DepartmentID <> m.DepartmentID: Filters out employees whose department is different from their manager's department.

Example Data and Results

Assume the Employees table has the following data:

EmployeeID	Name	DepartmentID	ManagerID
1	Alice	101	3
2	Bob	102	3
3	Charlie	103	NULL
4	David	101	2
5	Eve	102	2
6	Frank	104	5

In this dataset:

• Alice (Department 101) reports to Charlie (Department 103).
• Bob (Department 102) also reports to Charlie (Department 103).
• David (Department 101) reports to Bob (Department 102).
• Eve (Department 102) reports to Bob (Department 102).
• Frank (Department 104) reports to Eve (Department 102).

The result of the query would be:

EmployeeID	EmployeeName	EmployeeDepartment	ManagerID	ManagerName	ManagerDepartment
1	Alice	101	3	Charlie	103
2	Bob	102	3	Charlie	103
4	David	101	2	Bob	102
5	Eve	102	2	Bob	102
6	Frank	104	5	Eve	102

This result shows employees who work in a different department from their manager.

You can execute this query in SQL Server Management Studio or any SQL query tool connected to your SQL Server to find employees who are not in the same department as their manager.