Round #589

Write a SQL query joining 4 tables (customers, orders, line_items, products) to produce a customer-order-product report.

**Customer-Order-Product Report SQL Query** ============================================= The following SQL query joins four tables (`customers`, `orders`, `line_items`, and `products`) to produce a customer-order-product report. ```sql SELECT c.customer_id, c.customer_name, o.order_id, o.order_date, p.product_id, p.product_name, li.quantity, li.unit_price, (li.quantity * li.unit_price) AS total_price FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id INNER JOIN line_items li ON o.order_id = li.order_id INNER JOIN products p ON li.product_id = p.product_id ORDER BY c.customer_id, o.order_id, p.product_id; ``` **Explanation** --------------- 1. We start by selecting the columns we want to include in our report. 2. We join the `customers` table with the `orders` table on the `customer_id` column. 3. We then join the `orders` table with the `line_items` table on the `order_id` column. 4. Finally, we join the `line_items` table with the `products` table on the `product_id` column. 5. We use the `INNER JOIN` clause to ensure that only rows with matching values in all four tables are included in the result set. 6. We calculate the `total_price` for each line item by multiplying the `quantity` and `unit_price` columns. 7. We sort the result set by `customer_id`, `order_id`, and `product_id` for easier reading. **Example Use Case** -------------------- Suppose we have the following data: `customers` table: | customer_id | customer_name | | --- | --- | | 1 | John Smith | | 2 | Jane Doe | `orders` table: | order_id | customer_id | order_date | | --- | --- | --- | | 1 | 1 | 2022-01-01 | | 2 | 1 | 2022-01-15 | | 3 | 2 | 2022-02-01 | `line_items` table: | order_id | product_id | quantity | unit_price | | --- | --- | --- | --- | | 1 | 1 | 2 | 10.99 | | 1 | 2 | 1 | 5.99 | | 2 | 3 | 3 | 7.99 | | 3 | 1 | 1 | 10.99 | `products` table: | product_id | product_name | | --- | --- | | 1 | Product A | | 2 | Product B | | 3 | Product C | Running the above SQL query would produce the following result: | customer_id | customer_name | order_id | order_date | product_id | product_name | quantity | unit_price | total_price | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 1 | John Smith | 1 | 2022-01-01 | 1 | Product A | 2 | 10.99 | 21.98 | | 1 | John Smith | 1 | 2022-01-01 | 2 | Product B | 1 | 5.99 | 5.99 | | 1 | John Smith | 2 | 2022-01-15 | 3 | Product C | 3 | 7.99 | 23.97 | | 2 | Jane Doe | 3 | 2022-02-01 | 1 | Product A | 1 | 10.99 | 10.99 |

```csharp using Microsoft.EntityFrameworkCore; using Microsoft.Extensions.Logging; public class CustomerOrderProductReport { private readonly ILogger _logger; private readonly DbContext _dbContext; public CustomerOrderProductReport(ILogger<CustomerOrderProductReport> logger, DbContext dbContext) { _logger = logger; _dbContext = dbContext; } public async Task<CustomerOrderProductReportResult> GenerateReportAsync() { try { var query = from c in _dbContext.Set<Customer>() join o in _dbContext.Set<Order>() on c.CustomerId equals o.CustomerId join li in _dbContext.Set<LineItem>() on o.OrderId equals li.OrderId join p in _dbContext.Set<Product>() on li.ProductId equals p.ProductId select new CustomerOrderProductReportResult { CustomerName = c.Name, OrderId = o.OrderId, ProductName = p.Name, Quantity = li.Quantity }; var results = await query.ToListAsync(); return results; } catch (Exception ex) { _logger.LogError(ex, "Error generating customer-order-product report"); throw; } } } public class CustomerOrderProductReportResult { public string CustomerName { get; set; } public int OrderId { get; set; } public string ProductName { get; set; } public int Quantity { get; set; } } public class Customer { public int CustomerId { get; set; } public string Name { get; set; } } public class Order { public int OrderId { get; set; } public int CustomerId { get; set; } } public class LineItem { public int LineItemId { get; set; } public int OrderId { get; set; } public int ProductId { get; set; } public int Quantity { get; set; } } public class Product { public int ProductId { get; set; } public string Name { get; set; } } ``` This C# code defines a `CustomerOrderProductReport` class that generates a report by joining four tables: `customers`, `orders`, `line_items`, and `products`. The report includes the customer name, order ID, product name, and quantity. The `GenerateReportAsync` method uses Entity Framework Core to execute a LINQ query that joins the four tables based on their relationships. The results are then returned as a list of `CustomerOrderProductReportResult` objects. Error handling is implemented using a try-catch block, which logs any exceptions that occur during report generation using an `ILogger` instance. The `CustomerOrderProductReport` class is designed to be used in a dependency injection (DI) context, where the `ILogger` and `DbContext` instances are injected through the constructor. Note that this code assumes the existence of a `DbContext` class that defines the database context and the four tables as `DbSet` properties. The `Customer`, `Order`, `LineItem`, and `Product` classes represent the entities that correspond to the four tables.