Line Items with Product Categories — Joining Multiple Tables in SQL

The problem

Brightlane's merchandising team wants a complete breakdown of what customers have purchased, including the category each product belongs to.

Write a query to return the customer name, product name, and category name for every line item where the product has a resolving category.

Assumptions:

The chain reaches: orders → customers, orders → order_items → products → categories.
Line items whose product has no resolving category are excluded from the result.

Output:

One row per line item with a resolving category, with columns customer_name, product_name, and category_name.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  c.name AS customer_name,
  p.name AS product_name,
  cat.name AS category_name
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
  JOIN categories cat ON p.category_id = cat.id

The shape

Five tables, four JOINs, every one of them an inner match. The chain reaches from orders outward to customers on one side and through order_items to products to categories on the other. Line items whose product has no category_id (or whose category_id doesn't match any row in categories) drop out at the final join.

Clause by clause

SELECT c.name AS customer_name, p.name AS product_name, cat.name AS category_name pulls a name from three different tables. Three aliases are mandatory — without them, every reference would be ambiguous. cat rather than c for categories, because c is already taken by customers.
FROM orders o anchors the chain on orders.
JOIN customers c ON o.customer_id = c.id attaches the customer.
JOIN order_items oi ON o.id = oi.order_id attaches the line items. This is the row-multiplying step.
JOIN products p ON oi.product_id = p.id resolves each line item to its product.
JOIN categories cat ON p.category_id = cat.id resolves each product to its category. As an INNER JOIN, this hop silently drops any line item whose product has a missing or unmatched category_id. The prompt's contract — "where the product has a resolving category" — is exactly what this join type enforces.

Why this and not `LEFT JOIN` on the last hop

A LEFT JOIN would keep the line items whose products have no resolving category and show their category_name as missing. That's a different answer to a different question. The prompt scopes the result to line items with a resolving category, which is the inner-join semantic — drop the rows that don't match. The next problem (N022-H2) is the same chain with the optionality flipped, so the right join type comes from reading the prompt, not from a default.

You practiced a five-table chain composed entirely of inner matches. The shape scales without ceremony — each new table adds one more matching condition; alias discipline keeps the result readable.

Return the customer name, product name, and category name for every line item where the product has a resolving category

The shape

Clause by clause

Why this and not `LEFT JOIN` on the last hop

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

The shape

Clause by clause

Why this and not LEFT JOIN on the last hop

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

Why this and not `LEFT JOIN` on the last hop