Round #671

Use EXISTS to find customers with at least one cancelled order.

To find customers who have at least one cancelled order using SQL, you can use the `EXISTS` clause. Below is an example of how you might write this query: ```sql SELECT c.CustomerID, c.CustomerName FROM Customers c WHERE EXISTS ( SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID AND o.OrderStatus = 'Cancelled' ); ``` ### Explanation: - **Customers (c)**: This is the main table from which we are selecting customer details. - **Orders (o)**: This is a related table that contains order information, including the `CustomerID` and `OrderStatus`. - **EXISTS**: This clause checks if there exists at least one row in the `Orders` table where the `CustomerID` matches the current customer (`c.CustomerID`) and the `OrderStatus` is 'Cancelled'. - **SELECT 1**: The subquery simply selects a constant value (1) to indicate that a matching order was found. This is efficient because it stops searching as soon as it finds one match. ### Example Tables: For clarity, here are example structures for the `Customers` and `Orders` tables: **Customers Table:** ```sql CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, CustomerName NVARCHAR(100), ContactNumber NVARCHAR(20) ); ``` **Orders Table:** ```sql CREATE TABLE Orders ( OrderID INT PRIMARY KEY, CustomerID INT, OrderDate DATETIME, OrderStatus NVARCHAR(50), FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ); ``` ### Sample Data: ```sql INSERT INTO Customers (CustomerID, CustomerName, ContactNumber) VALUES (1, 'John Doe', '123-456-7890'), (2, 'Jane Smith', '098-765-4321'); INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderStatus) VALUES (101, 1, '2023-04-01', 'Completed'), (102, 1, '2023-04-02', 'Cancelled'), (103, 2, '2023-04-03', 'Completed'); ``` ### Result: Running the query will return: ```sql CustomerID | CustomerName -----------|-------------- 1 | John Doe ``` This indicates that only `John Doe` has at least one cancelled order.

To find customers who have at least one cancelled order using SQL's `EXISTS` clause, you can write a query that checks for the existence of any cancelled orders associated with each customer. Below is an example implementation in C# using Entity Framework Core (EF Core), which is a popular ORM for .NET applications. ### Step 1: Define the Models First, define your entity models assuming you have `Customer` and `Order` entities: ```csharp public class Customer { public int Id { get; set; } public string Name { get; set; } public ICollection<Order> Orders { get; set; } = new List<Order>(); } public class Order { public int Id { get; set; } public int CustomerId { get; set; } public OrderStatus Status { get; set; } public Customer Customer { get; set; } } public enum OrderStatus { Pending, Shipped, Cancelled } ``` ### Step 2: Configure the DbContext Next, configure your `DbContext` to include the relationships between `Customer` and `Order`: ```csharp public class AppDbContext : DbContext { public DbSet<Customer> Customers { get; set; } public DbSet<Order> Orders { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer("YourConnectionStringHere"); } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<Order>() .HasOne(o => o.Customer) .WithMany(c => c.Orders) .HasForeignKey(o => o.CustomerId); } } ``` ### Step 3: Write the Query Using `EXISTS` Now, write a query to find customers who have at least one cancelled order using the `EXISTS` clause: ```csharp using (var context = new AppDbContext()) { var customersWithCancelledOrders = context.Customers .Where(c => c.Orders.Any(o => o.Status == OrderStatus.Cancelled)) .ToList(); foreach (var customer in customersWithCancelledOrders) { Console.WriteLine($"Customer: {customer.Name}"); } } ``` ### Explanation 1. **Models**: We define `Customer` and `Order` entities with their respective properties and navigation properties. 2. **DbContext**: The `AppDbContext` class is configured to connect to a SQL Server database and includes the necessary entity configurations. 3. **Query**: The query uses LINQ to filter customers based on whether they have any orders with a status of `Cancelled`. The `Any` method is used to check for the existence of such orders. This approach ensures that you are leveraging Entity Framework Core's capabilities to perform database queries in a type-safe and efficient manner.