How to Join Three Tables in SQL
To list the products in every order you need three tables: orders, order items and products. Add one JOIN at a time, each with its own ON, and follow the keys from table to table.
Step by step
Three tables
- One answer, three tables. Which products were in each order? It sounds simple, but the answer is spread over three tables. Here's how to join them, one step at a time.
- The link table. Orders don't hold products, because one order can have many. Order items is the link. It has one row per product in an order, with an order ID and a product ID.
First join
- Orders to items. First, two tables. Orders as o, joined to order items as i, where the order ID matches. Show the order ID, the product ID and the quantity. Three hundred and seven rows. One row for each product in each order.
SELECT o.order_id, i.product_id, i.quantity FROM orders AS o JOIN order_items AS i ON i.order_id = o.order_id; - Just a number. But the product ID is only a number. Nobody knows what product ten is. The names live in the products table.
Second join
- Items to products. So add a second join. Remove the semicolon, and join products as p on a new line. Every join needs its own on. Products match order items on the product ID. That's the key the two tables share.
o.order_id JOIN products AS pON p.product_id = i.product_id; - Show the name. Now swap the product ID at the top for the product name, from p. Run it. Every order, with the names of the products in it.
p.product_name, - Names from products. These names came from the third table, found through the product ID in order items.
- Same row count. Still three hundred and seven rows. Each item has exactly one product, so the extra join added a column, not rows.
Recap
- Join three tables. So, to join three tables, add one join at a time, each with its own on. Follow the keys, orders to order items, then order items to products.
