Order Lines with Customer and Quantity — Joining Multiple Tables in SQL

The problem

Brightlane's sales team needs a line-item report showing each order alongside the customer who placed it and the quantity of each item in that order.

Write a query to return the order ID, customer name, and quantity for every order line.

Assumptions:

The result row count is one row per order line item (not one row per order).

Output:

One row per order line, with columns order_id, customer_name, and quantity.

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
  o.id AS order_id,
  c.name AS customer_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

The shape

Two JOINs chain three tables on shared keys — orders to customers by customer_id, then orders to order_items by order_id. The result lands at one row per line item, because order_items is the most-multiplying table in the chain.

Clause by clause

SELECT o.id AS order_id, c.name AS customer_name, oi.quantity picks one column from each of the three tables. Every reference is alias-prefixed, which is the only way the bare names id and name stay unambiguous in a chain where both orders and customers have a column called id.
FROM orders o anchors the chain on orders and aliases the table as o.
JOIN customers c ON o.customer_id = c.id attaches the customer to each order through the foreign key. Each order has one customer, so this join doesn't change the row count.
JOIN order_items oi ON o.id = oi.order_id then attaches every line item belonging to that order. This is the multiplying step — an order with three items becomes three rows. The first row pair for Alice Nguyen's order 1 shows two rows because that order has two line items.

You practiced chaining three tables in a single FROM clause with two JOIN clauses. The recurring shape: each JOIN adds one table; the ON clause specifies how that new table connects to the working set so far.

Return the order ID, customer name, and quantity for every order line

The shape

Clause by clause

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