Brightlane's operations team wants a complete line-item report linking every order to the purchasing customer, the product bought, and that product's category. Some products do not have an assigned category — those line items must still appear in the output, with the category name missing.
Write a query to return the customer name, product name, category name, and quantity for every line item.
Assumptions:
- The chain reaches:
orders→customers,orders→order_items→products, thenproducts→categories. - Every order has a customer; every order line has an order and a product; these relationships are clean one-to-many.
- A product may have an unresolved
category_id. Those line items must still appear in the result, with the category name missing.
Output:
- One row per line item, with columns
customer_name,product_name,category_name, andquantity. Rows for line items with no resolving category will have a missingcategory_name.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
c.name AS customer_name,
p.name AS product_name,
cat.name AS category_name,
oi.quantity
FROM
orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
LEFT JOIN categories cat ON p.category_id = cat.idThe shape
Three INNER JOINs carry the chain through the relationships the prompt guarantees. One LEFT JOIN on the last hop preserves line items whose product has no resolving category. The mixed chain is the literal expression of the prompt's contract: every order has a customer, every line has a product, but a product may have no category.
Clause by clause
SELECT c.name AS customer_name, p.name AS product_name, cat.name AS category_name, oi.quantitypulls one column from each of four tables. The alias prefix on every reference keeps the threenamecolumns straight, andcatis used rather thancfor categories becausecis already taken.FROM orders oanchors the chain.JOIN customers c ON o.customer_id = c.idis inner because every order has a customer.JOIN order_items oi ON o.id = oi.order_idis inner because every line item belongs to an order. This is the row-multiplying step.JOIN products p ON oi.product_id = p.idis inner because every line item points at a real product.LEFT JOIN categories cat ON p.category_id = cat.idis left because a product may have no resolving category. Line items whose product has no category survive, withcat.nameset toNULL.
Why this and not all LEFT JOINs
LEFT JOIN everywhere would still return the right rows on this dataset, because the other three relationships are guaranteed clean. But the join type is meant to encode that guarantee, not paper over a missing one. INNER JOIN says the right side is required. LEFT JOIN says the right side is optional. Using LEFT JOIN for a required relationship is a small lie in the query: it tells the next reader something is optional when it isn't.
The trap
The opposite mistake is making the last hop an INNER JOIN and silently losing line items whose product has no category. There would be no error. The result would look smaller than the actual line-item population, and the missing rows would never surface unless someone counted line items directly. The way to catch it is to read every INNER JOIN in the chain and ask whether the right side is genuinely guaranteed. If not, the join type is wrong for the contract.
You practiced mixing INNER JOIN and LEFT JOIN in a single chain. Use INNER JOIN when the relationship is guaranteed; reach for LEFT JOIN only where the right side is genuinely optional.
