Order Lines with Product Names — Joining Multiple Tables in SQL

The problem

Brightlane's product team wants to see each order line item alongside the name of the product it represents.

Write a query to return the order ID, product name, and quantity for every line item on record.

Output:

One row per order line item, with columns order_id, product_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,
  p.name AS product_name,
  oi.quantity
FROM
  orders o
  JOIN order_items oi ON o.id = oi.order_id
  JOIN products p ON oi.product_id = p.id

The shape

The chain pivots through order_items, the junction table — each line item connects on one side to its order and on the other to its product. Two JOINs on shared keys land the result at one row per line item with the product's name attached.

Clause by clause

SELECT o.id AS order_id, p.name AS product_name, oi.quantity picks one column from each table. p.name is the product name; o.id is the order ID. Without the aliases, both names would be ambiguous — every table in the schema has an id, and both products and others have a name.
FROM orders o anchors the chain on orders.
JOIN order_items oi ON o.id = oi.order_id attaches the line items. This is the multiplying step: an order with three line items becomes three rows in the intermediate result.
JOIN products p ON oi.product_id = p.id then resolves each line item to its product. Each line item points at exactly one product, so this join enriches the rows without multiplying them further. The row count is settled by the order_items join above.

You practiced a three-table chain through a junction (order_items) to its two parent tables. The recurring shape: junctions almost always connect to multiple dimensions, so they're frequently the middle node in a multi-table join.

Return the order ID, product name, and quantity for every line item on record

The shape

Clause by clause

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