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_idSchema · Garden ShopTable · order_items5 columns · 48 rows
Table · order_items
5 columns · 48 rowsOne row per line item within an order.
order_item_id intorder_id intproduct_id intquantity intunit_price decimal
Your task
Show the order id, order date,product name, quantity, and calculatedline total for orders 1 through 3.
SQL Workbench
⌘↵ to run·
Expected answer
- Columns: order_id, order_date, product_name, quantity, line_total.
- Rows: 6 line items for orders 1, 2, and 3.
- Order 1 should include Fiddle-Leaf Fig and Potting Soil 20L.