Round #660

Write a self-join to find pairs of employees with the same manager.

Certainly! To find pairs of employees who have the same manager, you can use a self-join in SQL. Below is an example query that demonstrates how to achieve this: ```sql SELECT e1.EmployeeID AS Employee1ID, e1.FirstName AS Employee1FirstName, e1.LastName AS Employee1LastName, e2.EmployeeID AS Employee2ID, e2.FirstName AS Employee2FirstName, e2.LastName AS Employee2LastName FROM Employees e1 JOIN Employees e2 ON e1.ManagerID = e2.ManagerID AND e1.EmployeeID < e2.EmployeeID ORDER BY e1.ManagerID, e1.EmployeeID; ``` ### Explanation: - **e1** and **e2** are aliases for the same table `Employees`. - The `JOIN` condition `e1.ManagerID = e2.ManagerID` ensures that we only consider employees who have the same manager. - The additional condition `e1.EmployeeID < e2.EmployeeID` is used to avoid duplicate pairs (i.e., (A, B) and (B, A)) and ensure that each pair is listed only once. ### Example Data: Assume you have an `Employees` table with the following data: | EmployeeID | FirstName | LastName | ManagerID | |------------|-----------|----------|-----------| | 1 | John | Doe | NULL | | 2 | Jane | Smith | 1 | | 3 | Alice | Johnson | 1 | | 4 | Bob | Brown | 2 | ### Result: The query will return the following pairs of employees who have the same manager: | Employee1ID | Employee1FirstName | Employee1LastName | Employee2ID | Employee2FirstName | Employee2LastName | |-------------|--------------------|-------------------|-------------|--------------------|-------------------| | 2 | Jane | Smith | 3 | Alice | Johnson | This result shows that Jane and Alice both report to John (ManagerID = 1).

To implement a self-join to find pairs of employees with the same manager, we can use LINQ (Language Integrated Query) in C#. This approach allows us to join the `Employee` table with itself based on the manager's ID and then filter out the pairs where the employee IDs are different. Here is the complete code: ```csharp using System; using System.Collections.Generic; using System.Linq; public class Employee { public int EmployeeId { get; set; } public string Name { get; set; } public int ManagerId { get; set; } } public class Program { public static void Main() { // Sample data var employees = new List<Employee> { new Employee { EmployeeId = 1, Name = "Alice", ManagerId = 101 }, new Employee { EmployeeId = 2, Name = "Bob", ManagerId = 101 }, new Employee { EmployeeId = 3, Name = "Charlie", ManagerId = 102 }, new Employee { EmployeeId = 4, Name = "David", ManagerId = 102 }, new Employee { EmployeeId = 5, Name = "Eve", ManagerId = 103 } }; // Self-join to find pairs of employees with the same manager var employeePairs = from e1 in employees join e2 in employees on e1.ManagerId equals e2.ManagerId where e1.EmployeeId < e2.EmployeeId select new { Employee1 = e1, Employee2 = e2 }; // Output the results foreach (var pair in employeePairs) { Console.WriteLine($"{pair.Employee1.Name} and {pair.Employee2.Name} have the same manager."); } } } ``` ### Explanation: 1. **Employee Class**: This class represents an employee with properties for `EmployeeId`, `Name`, and `ManagerId`. 2. **Sample Data**: We create a list of employees to work with. 3. **Self-Join Query**: - We use LINQ's `join` clause to join the `employees` table with itself (`e1` and `e2`) based on the `ManagerId`. - The `where e1.EmployeeId < e2.EmployeeId` condition ensures that each pair is only listed once (i.e., it avoids duplicate pairs like `(Alice, Bob)` and `(Bob, Alice)`). 4. **Output**: We iterate through the resulting pairs and print out the names of employees who have the same manager. This code demonstrates how to perform a self-join in LINQ to find pairs of employees with the same manager efficiently.