Round #575

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

```csharp using System; using System.Data; using System.Linq; public class Employee { public int Id { get; set; } public string Name { get; set; } public int ManagerId { get; set; } } class Program { static void Main() { // Create a sample DataTable var employees = new[] { new Employee { Id = 1, Name = "John", ManagerId = 101 }, new Employee { Id = 2, Name = "Alice", ManagerId = 101 }, new Employee { Id = 3, Name = "Bob", ManagerId = 102 }, new Employee { Id = 4, Name = "Mary", ManagerId = 103 }, new Employee { Id = 5, Name = "Peter", ManagerId = 101 }, }; // Perform a self-join to find pairs of employees with the same manager var pairs = from e1 in employees from e2 in employees where e1.ManagerId == e2.ManagerId && e1.Id < e2.Id select new { Employee1 = e1.Name, Employee2 = e2.Name }; // Print the pairs foreach (var pair in pairs) { Console.WriteLine($"{pair.Employee1} and {pair.Employee2} have the same manager."); } } } ```

```csharp using System; using System.Data; using System.Data.SqlClient; public class Employee { public int Id { get; set; } public string Name { get; set; } public int ManagerId { get; set; } } public class EmployeePair { public string Employee1Name { get; set; } public string Employee2Name { get; set; } public int ManagerId { get; set; } } public class EmployeeRepository { private readonly string _connectionString; public EmployeeRepository(string connectionString) { _connectionString = connectionString; } public DataTable GetEmployees() { using (var connection = new SqlConnection(_connectionString)) { connection.Open(); var command = new SqlCommand("SELECT Id, Name, ManagerId FROM Employees", connection); var adapter = new SqlDataAdapter(command); var table = new DataTable(); adapter.Fill(table); return table; } } public DataTable GetEmployeePairs() { using (var connection = new SqlConnection(_connectionString)) { connection.Open(); var query = @" SELECT e1.Name AS Employee1Name, e2.Name AS Employee2Name, e1.ManagerId FROM Employees e1 INNER JOIN Employees e2 ON e1.ManagerId = e2.ManagerId WHERE e1.Id < e2.Id "; var command = new SqlCommand(query, connection); var adapter = new SqlDataAdapter(command); var table = new DataTable(); adapter.Fill(table); return table; } } } class Program { static void Main(string[] args) { var connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;"; var repository = new EmployeeRepository(connectionString); var employeePairs = repository.GetEmployeePairs(); foreach (DataRow row in employeePairs.Rows) { Console.WriteLine($"Employee 1: {row["Employee1Name"]}, Employee 2: {row["Employee2Name"]}, Manager Id: {row["ManagerId"]}"); } } } ``` This C# code snippet demonstrates how to perform a self-join on an `Employees` table to find pairs of employees with the same manager. Here's a step-by-step explanation: 1. **Define the `Employee` class**: This class represents an employee with properties for `Id`, `Name`, and `ManagerId`. 2. **Define the `EmployeePair` class**: This class represents a pair of employees with the same manager, containing properties for `Employee1Name`, `Employee2Name`, and `ManagerId`. 3. **Create an `EmployeeRepository` class**: This class encapsulates database operations related to employees. It has a constructor that takes a connection string and methods to retrieve employees and employee pairs. 4. **Implement the `GetEmployees` method**: This method retrieves all employees from the `Employees` table. 5. **Implement the `GetEmployeePairs` method**: This method performs a self-join on the `Employees` table to find pairs of employees with the same manager. The self-join is achieved using an `INNER JOIN` clause with the condition `e1.ManagerId = e2.ManagerId`. To avoid duplicate pairs, the condition `e1.Id < e2.Id` is added to the `WHERE` clause. 6. **Create a `Program` class**: This class contains the `Main` method, which demonstrates how to use the `EmployeeRepository` class to retrieve employee pairs and print them to the console. Note that you should replace the connection string in the `Main` method with your actual database connection string.