SQL Queries to Identify Employees Working Under Multiple Managers

-- Create the table
CREATE TABLE Employee_Manager (
    employee_id INT,
    manager_id INT,
    department VARCHAR(50),
    person_name VARCHAR(100)
);

3. Inserting Sample Data

To demonstrate our queries, we’ll insert sample data into the Employee_Manager table.

INSERT INTO Employee_Manager (employee_id, manager_id, department, person_name) VALUES
(1, 101, 'Sales', 'Shivan'),
(1, 102, 'Sales', 'Shivan'),
(2, 101, 'Sales', 'Shubham'),
(3, 103, 'Marketing', 'Kumar'),
(4, 104, 'IT', 'Preeti'),
(4, 105, 'IT', 'David'),
(5, 106, 'HR', 'Preeti'),
(6, 107, 'Finance', 'Sanju'),
(7, 108, 'Operations', 'Mukesh'),
(7, 109, 'Operations', 'Mukesh'),
(8, 110, 'Sales', 'Rohit'),
(9, 111, 'Marketing', 'Anjali'),
(9, 112, 'Marketing', 'Anjali'),
(10, 113, 'IT', 'Ravi'),
(11, 114, 'Finance', 'Nisha'),
(12, 115, 'HR', 'Ritu'),
(12, 116, 'HR', 'Ritu'),
(13, 117, 'Operations', 'Amit'),
(14, 118, 'Sales', 'Suman'),
(14, 119, 'Sales', 'Suman'),
(15, 120, 'Marketing', 'Raj'),
(16, 121, 'IT', 'Kavita'),
(17, 122, 'Finance', 'Deepak'),
(17, 123, 'Finance', 'Deepak'),
(18, 124, 'HR', 'Neha'),
(19, 125, 'Operations', 'Vikram'),
(20, 126, 'Sales', 'Alok'),
(21, 127, 'Marketing', 'Meena'),
(22, 128, 'IT', 'Sameer'),
(23, 129, 'Finance', 'Nitin'),
(24, 130, 'HR', 'Seema'),
(24, 131, 'HR', 'Seema'),
(25, 132, 'Operations', 'Tarun'),
(26, 133, 'Sales', 'Monica'),
(27, 134, 'Marketing', 'Rahul'),
(28, 135, 'IT', 'Pooja'),
(28, 136, 'IT', 'Pooja'),
(29, 137, 'Finance', 'Manoj'),
(30, 138, 'Operations', 'Tina');

4. Solution 1: Using GROUP BY and HAVING

The GROUP BY and HAVING clauses help us group data and filter out groups based on specific conditions.

SELECT employee_id, person_name, department
FROM Employee_Manager
GROUP BY employee_id, person_name, department
HAVING COUNT(DISTINCT manager_id) > 1;

Detailed Explanation:

5. Solution 2: Using a Self Join

A self-join compares rows within the same table to find matches.

SELECT DISTINCT e1.employee_id, e1.person_name, e1.department
FROM Employee_Manager e1
JOIN Employee_Manager e2 ON e1.employee_id = e2.employee_id
WHERE e1.manager_id <> e2.manager_id;

Detailed Explanation:

• Self-Join: The query joins the Employee_Manager table with itself to find pairs of records for the same employee_id.
• Different Managers: The WHERE clause filters these pairs to include only those where manager_iddiffers, identifying employees with multiple managers.
• Distinct Results: The DISTINCTkeyword ensures each employee appears only once in the final result set.
• Output: The query selects employee_id, person_name, and departmentfor employees who have worked under more than one manager.

6. Solution 3: Using a Subquery

A subquery calculates the number of managers for each employee separately.

SELECT employee_id, person_name, department
FROM (
    SELECT employee_id, person_name, department, COUNT(DISTINCT manager_id) AS manager_count
    FROM Employee_Manager
    GROUP BY employee_id, person_name, department
) AS subquery
WHERE manager_count > 1;

Detailed Explanation:

• The inner query (SELECT employee_id, person_name, department, COUNT(DISTINCT manager_id) AS manager_count FROM Employee_Manager GROUP BY employee_id, person_name, department) calculates the number of unique managers each employee has worked under by grouping the data.
• The subquery generates a temporary result set that includes each employee’s ID, name, department, and the count of distinct managers they have worked with (manager_count).
• The outer query (SELECT employee_id, person_name, department FROM (...) AS subquery WHERE manager_count > 1) filters this result set to select only those employees who have a manager_count greater than 1, indicating they have worked under more than one manager.
• The final result retrieves the employee_id, person_name, and department for all employees who have been associated with multiple managers.