Brightlane's catalog team needs a list of every product alongside any order line items where the ordered quantity was greater than 1.
Write a query to return each product alongside the ID of any qualifying order line item.
Assumptions:
- A qualifying line is one whose
quantityis greater than1. - Every product must appear. Products with qualifying lines contribute one row per qualifying line. Products with no qualifying lines contribute a single row with a missing
order_item_id.
Output:
- One row per product-qualifying-line pairing, plus one row per product with no qualifying lines, with columns
nameandorder_item_id.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
p.name,
oi.id AS order_item_id
FROM
products p
LEFT JOIN order_items oi ON p.id = oi.product_id
AND oi.quantity > 1The shape
Moving oi.quantity > 1 into the ON clause keeps every product in the catalog while restricting which order line items are allowed to attach. A product with no qualifying line still appears, with order_item_id missing; a product with multiple qualifying lines contributes one row per line.
Clause by clause
SELECT p.name, oi.id AS order_item_idreturns each product's name alongside the ID of any qualifying line attached on that row. When the join attaches nothing,oi.idis missing.FROM products p LEFT JOIN order_items oi ON p.id = oi.product_id AND oi.quantity > 1pairs each product with its qualifying lines. TheONclause carries two conjoined conditions: the product-to-line link, and the quantity restriction. A line attaches only when both are true; otherwise the product still appears, with everyoi.* column missing.HDMI Cable 2mappears twice in the result because two separate line items qualified; the catalog's other products either contributed a single qualifying line (likeWriting Clean Code) or no qualifying line at all and surface withorder_item_idmissing.
Why this and not WHERE oi.quantity > 1
The WHERE version would convert the LEFT JOIN back into an inner join on this predicate. Every unmatched product has a missing oi.quantity, and NULL > 1 is not true, so the row fails the filter. Products with no qualifying line — the majority of the catalog — would disappear from the report entirely. Products with no order lines at all would also disappear, for the same missing-value reason. The version in ON is the only one that preserves the full catalog.
The trap
A numeric comparison like > 1 looks safer than an equality check because there is no literal placeholder to worry about. The missing-value behavior is the same as with any other comparison: NULL > 1 returns missing, and a WHERE clause that returns missing drops the row. The unmatched rows the LEFT JOIN was specifically preserving are exactly the rows where oi.quantity is missing, so a WHERE filter on oi.quantity is guaranteed to drop them. The placement of any condition on a right-side column decides whether unmatched rows survive — ON keeps them, WHERE drops them.
You practiced moving a numeric-range condition into the ON clause — every left record appears, with matching right rows attached or a missing value when none qualify.
