Order Items by Product Category — INNER JOIN in SQL

The problem

Brightlane's category team wants to audit order-line activity for a specific product grouping.

Write a query to return the product name and quantity for every order line item whose product is assigned to category 6.

Assumptions:

The order_items table contains one row per product per order.
The products table contains every product in the catalogue; category membership is recorded on products.category_id.
The category condition applies to the product record — order lines themselves carry no category information.

Output:

One row per qualifying line item, with columns 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
  p.name AS product_name,
  oi.quantity
FROM
  order_items oi
  JOIN products p ON oi.product_id = p.id
WHERE
  p.category_id = 6

The shape

The category_id lives on the product record, not on the order line. The join brings the product's category_id into scope on every line, and WHERE p.category_id = 6 then narrows the assembled rows to lines whose product belongs to that category.

Clause by clause

FROM order_items oi reads the line-items table. Each row here is one product on one order; the only product attribute it carries is product_id.
JOIN products p ON oi.product_id = p.id pairs each line with its product, attaching the full product record — name, price, category_id, everything — to the line.
WHERE p.category_id = 6 filters the assembled rows. The condition is on the product side because that's where category_id lives; the order line itself has no category attribute to filter on. Only lines whose product belongs to category 6 survive — the Crest Pro 14", Crest Air, Meridian T1 Carbon, and Volt XS 13 lines from the laptops category.
SELECT p.name AS product_name, oi.quantity returns the product name from the product side and the quantity from the line itself.

Why this and not filter `order_items` directly

The order line stores product_id but not category_id. There's no oi.category_id column to filter on, and the relationship between a product and its category is recorded once on the product record, not duplicated onto every line that sells that product. The only way to express "lines whose product is in category 6" is to join to products first, putting p.category_id in scope, and then filter on it.

This is the recurring pattern when the criterion lives one table away from the rows being reported. Join in the dimension that carries the attribute, then filter on the dimension. The fact table contributes the rows; the dimension contributes the filter column.

You practiced narrowing a fact table (line items) by an attribute that lives only on a related dimension (the product's category). The recurring shape any time the narrowing criterion isn't directly on the table being reported.

Return the product name and quantity for every order line item whose product is assigned to category `6`

The shape

Clause by clause

Why this and not filter `order_items` 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 filter order_items directly

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

Why this and not filter `order_items` directly