Delivered Order Line Items — Joining Multiple Tables in SQL

The problem

Brightlane's fulfilment team wants a line-item breakdown for delivered orders only, showing what products were included and at what price.

Write a query to return the customer name, product name, and unit price for every item in a delivered order.

Assumptions:

The chain reaches: orders → customers, orders → order_items → products.
A delivered order has status = 'delivered'; the condition applies to the order side of the chain and removes line items belonging to non-delivered orders.

Output:

One row per qualifying line item, with columns customer_name, product_name, and unit_price.

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,
  oi.unit_price
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
WHERE
  o.status = 'delivered'

The shape

The same three-table chain as the easies, with one WHERE predicate on the central table narrowing the entire result. o.status = 'delivered' runs against orders and drops every line item belonging to a non-delivered order — line items inherit their order's status by virtue of being chained to it.

Clause by clause

SELECT c.name AS customer_name, p.name AS product_name, oi.unit_price picks one column from each of the three non-junction tables.
FROM orders o anchors the chain.
JOIN customers c ON o.customer_id = c.id attaches the customer to each order.
JOIN order_items oi ON o.id = oi.order_id attaches the line items — the multiplying step.
JOIN products p ON oi.product_id = p.id resolves each line item to its product.
WHERE o.status = 'delivered' filters the joined result down to rows whose underlying order has status = 'delivered'. The predicate sits on the orders side of the chain, but it shapes the final row set because every line item is tied to one order — drop an order, and every line item attached to it drops with it.

Why this and not putting `status` in the `ON` clause

Both shapes return the same rows here, because the chain is all INNER JOINs and an inner join treats ON conditions and WHERE conditions identically. The convention is to put join keys (the fk = pk shape) in the ON clause and row-level filters in the WHERE clause, because that's what each clause is for — ON says how the tables connect, WHERE says which rows survive. Mixing them works for inner joins and breaks for outer joins; getting in the habit now prevents the silent bug later.

You practiced narrowing a multi-table chain by an attribute on the central table (orders.status). A WHERE clause on any single table in the chain narrows the entire result.

Return the customer name, product name, and unit price for every item in a delivered order

The shape

Clause by clause

Why this and not putting `status` in the `ON` clause

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 putting status in the ON clause

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

Why this and not putting `status` in the `ON` clause