Line Items with Customer and Product — Joining Multiple Tables in SQL

The problem

Brightlane's finance team wants a detailed line-item report linking each order back to the customer who placed it and the specific product purchased.

Write a query to return the customer name, product name, quantity, and unit price for every line item.

Assumptions:

The orders, customers, order_items, and products tables together describe every line item.
orders.customer_id → customers.id; order_items.order_id → orders.id; order_items.product_id → products.id.
The result row count is one row per line item.

Output:

One row per line item, with columns customer_name, product_name, quantity, 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.quantity,
  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

The shape

A four-table chain pivots on orders. customers sits on one side, order_items on the other, and products is reached one further hop through the line items. The result lands at one row per line item, because order_items is the most-multiplying table.

Clause by clause

SELECT c.name AS customer_name, p.name AS product_name, oi.quantity, oi.unit_price picks one column from customers, one from products, and two from order_items. Both customers and products have a column called name, so the aliases are doing real disambiguation work — without them PostgreSQL would refuse to resolve the reference.
FROM orders o anchors the chain.
JOIN customers c ON o.customer_id = c.id attaches the customer to each order. One-to-one with the order side, no row multiplication.
JOIN order_items oi ON o.id = oi.order_id is the multiplying join — every line item on the order becomes its own row. Alice Nguyen's order 1 had two items, so her name appears twice in the output.
JOIN products p ON oi.product_id = p.id resolves each line item to its product. Each line item points at exactly one product, so this last hop enriches the rows without multiplying them.

Why this and not joining `products` to `orders` directly

There is no product_id on orders. The relationship from an order to its products only exists through order_items — that's why the junction table exists in the first place. An order can contain many products, and a product can appear on many orders; the junction is what stores that many-to-many relationship as two clean one-to-many hops. Skipping order_items is structurally impossible, not just stylistically wrong.

You practiced a four-table chain where one table (orders) sits between three others. The recurring shape: an order is the natural pivot — customers sit on one side, line items on another, and products are reached through the line items.

Return the customer name, product name, quantity, and unit price for every line item

The shape

Clause by clause

Why this and not joining `products` to `orders` directly

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 joining products to orders directly

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

Why this and not joining `products` to `orders` directly