SQLMaxx
Sign in Start free
N022-H1 Tier 2 · Core SQL · hard ecommerce · Brightlane

Return the customer name, order ID, and item ID for every row in the result

Part of Joining Multiple Tables in SQL

The problem

Brightlane's customer success team wants a complete record of every customer, every order they've placed, and every item in those orders. Customers with no orders must still appear in the output. Orders with no items must still appear too.

Write a query to return the customer name, order ID, and item ID for every row in the result.

Assumptions:

  • The chain links three tables: customers → orders → order_items.
  • The result is anchored on the customer side. A customer with no orders appears once with order_id and item_id both missing. An order with no items appears with item_id missing.
  • A customer with multiple items contributes multiple rows.

Output:

  • One row per matched customer-order-item triple, plus one row per customer with no orders, plus one row per order with no items, with columns customer_name, order_id, and item_id.
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,
  o.id AS order_id,
  oi.id AS item_id
FROM
  customers c
  LEFT JOIN orders o ON c.id = o.customer_id
  LEFT JOIN order_items oi ON o.id = oi.order_id

The shape

Every link in the chain is a LEFT JOIN because every link must preserve unmatched rows on the left. A customer with no orders stays in the result with both order_id and item_id as NULL; an order with no items stays with item_id as NULL. The moment any link became an INNER JOIN, the rows it dropped would be gone for good — no later LEFT JOIN can recover them.

Clause by clause

  • SELECT c.name AS customer_name, o.id AS order_id, oi.id AS item_id picks the identifier from each level of the chain. The two id columns from orders and order_items are aliased so the result row exposes the chain's structure at a glance.
  • FROM customers c anchors the chain on the customer side. The choice of anchor matters: LEFT JOIN preserves rows from the left, so anchoring on customers is what makes "customers with no orders must still appear" possible.
  • LEFT JOIN orders o ON c.id = o.customer_id attaches every order for each customer. Customers with no orders survive, with o.id set to NULL on those rows.
  • LEFT JOIN order_items oi ON o.id = oi.order_id attaches every line item for each surviving order. Orders with no items survive, with oi.id set to NULL. Customer rows that already have o.id = NULL from the previous step pass through this join with oi.id also NULL, because NULL = NULL is unknown and never matches — the join condition fails, the LEFT JOIN keeps the row anyway, and oi.id lands as NULL.

The trap

The trap is mixing in a single INNER JOIN and assuming the later LEFT JOIN can recover what the inner one dropped. It cannot. Joins evaluate sequentially: each one operates on the intermediate result from the previous step. Once an INNER JOIN discards the no-orders customers, they are not in the intermediate result the next join sees, and no amount of left-side preservation can put them back. The way to keep the chain honest is to read each join in source order and ask, at every step, "does this join discard rows I need later?" If the answer is yes, the join type is wrong.

You practiced a chain of LEFT JOINs where every link must preserve unmatched rows. Once an INNER JOIN drops a row from the intermediate result, no later LEFT JOIN can recover it.

How you actually get good at SQL

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

That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.

A stack of SQL practice problem cards, the top card showing an employees table.
615 problems · 66 concepts

Real problems. Not toy examples.

615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.

A retro computer showing a SQL query marked correct with a green checkmark.
Instant AI feedback

Write a query. Know if it's right in one second.

No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.

A circular mastery progress dial filling from blue to green, the SQLMaxx diamond at its center.
Mastery tracking

Stop guessing whether you actually know it.

SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.

A SQL query editor circled by a blue return arrow with a clock, scheduled to come back for review.
Spaced review

Learn it once. Keep it for good.

Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.

Practice, feedback, mastery, review. That's the loop that turns reading into real skill.

Start free

No account, no credit card. Start solving in under a minute.