Round #806

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 System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using Microsoft.Extensions.Logging; public class CustomerOrderProductReport { private readonly DbContext _context; private readonly ILogger<CustomerOrderProductReport> _logger; public CustomerOrderProductReport(DbContext context, ILogger<CustomerOrderProductReport> logger) { _context = context; _logger = logger; } public async Task<IEnumerable<CustomerOrderProduct>> GetReportAsync() { try { var report = await _context.Customers .Join(_context.Orders, c => c.CustomerId, o => o.CustomerId, (c, o) => new { Customer = c, Order = o }) .Join(_context.LineItems, co => co.Order.OrderId, li => li.OrderId, (co, li) => new { co.Customer, co.Order, LineItem = li }) .Join(_context.Products, coli => coli.LineItem.ProductId, p => p.ProductId, (coli, p) => new CustomerOrderProduct { CustomerName = coli.Customer.Name, OrderId = coli.Order.OrderId, ProductName = p.Name, Quantity = coli.LineItem.Quantity }) .ToListAsync(); return report; } catch (Exception ex) { _logger.LogError(ex, "Error generating customer-order-product report"); throw; } } } 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; } } public class CustomerOrderProduct { public string CustomerName { get; set; } public int OrderId { get; set; } public string ProductName { get; set; } public int Quantity { get; set; } } ``` This C# code uses Entity Framework Core to join four tables (`Customers`, `Orders`, `LineItems`, and `Products`) and generate a customer-order-product report. The `CustomerOrderProductReport` class takes a `DbContext` and an `ILogger` in its constructor, and its `GetReportAsync` method performs the join operation and returns a list of `CustomerOrderProduct` objects. If an error occurs during the report generation, it is logged using the `ILogger`. Note: The SQL query equivalent to this join operation would be: ```sql SELECT c.Name AS CustomerName, o.OrderId, p.Name AS ProductName, li.Quantity FROM Customers c JOIN Orders o ON c.CustomerId = o.CustomerId JOIN LineItems li ON o.OrderId = li.OrderId JOIN Products p ON li.ProductId = p.ProductId ```