Lesson 5.2

Joining Orders to Products in SQL

Some relationships need a middle table before the result has all the details.

One order can contain many products, and one product can appear in many orders. That many-to-many relationship is stored in the order_items bridge table.

To show order details with product names, join orders to order_items, then join order_items to products.

Pattern

select a.id_column, b.bridge_column, c.detail_column
from table_a as a
join bridge_table as b
  on a.id_column = b.id_column
join table_c as c
  on b.other_id = c.other_id

A bridge is safe because each line item points at one order and one product. Joining two child tables of the same parent isn't: see why a many-to-many join inflates totals. And join on ids, not names: see why joining on names is risky.

Syntax reference

Need syntax details? See the SQL JOIN types reference

Schema · Garden Shop Tables · order_items, orders, products 3 tables

Table · order_items

One row per line item within an order.

5 columns · 48 rows

order_item_id int order_id int product_id int quantity int unit_price decimal

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Table · products

One row per product, with price, cost, and inventory levels.

9 columns · 24 rows

product_id int product_name text category_id int supplier_id int price decimal cost decimal quantity_on_hand int reorder_level int discontinued bool

Your task

Show the order id, order date, product name, quantity, and calculated line total for orders 1 through 3. Sort by order id, then product name.