Joining Multiple Tables
How to join three or more tables in one query by following the keys that link them, from customers to the categories of the products they ordered.
Journeyman · SQL · MySQL · Updated
You are not limited to joining only two tables in a query. If you need to retrieve data from multiple tables you can join them all, as long as you have a connecting relationship between the tables.
If I wanted to create a report that includes customer order data, including the customer name, but also the names of the categories that they ordered, I would need to join all related tables.

SELECT c.customer_id
, c.company_name
, cat.category_name
, p.product_name
, od.unit_price
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id
INNER JOIN order_details AS od
ON o.order_id = od.order_id
INNER JOIN products AS p
ON od.product_id = p.product_id
INNER JOIN categories AS cat
ON p.category_id = cat.category_id;
Do it: list what Ana Trujillo's shop ordered
Ana Trujillo's shop, whose customer_id is 'ANATR', has asked for a list of every product it has ordered: which order, which product, and how many.
The answer is spread over three tables:
orders: each order'sorder_id, and thecustomer_idof the customer who placed it.order_details: one row per order line, with itsorder_id, theproduct_idit sold, and thequantity.products: each product'sproduct_idandproduct_name.
They hold these rows (the Tables tab shows the SQL that makes them):
| order_id | customer_id |
|---|---|
| 10308 | ANATR |
| 10355 | AROUT |
| 10625 | ANATR |
| order_id | product_id | quantity |
|---|---|---|
| 10308 | 69 | 1 |
| 10308 | 70 | 5 |
| 10355 | 24 | 25 |
| 10355 | 57 | 25 |
| 10625 | 14 | 3 |
| 10625 | 42 | 5 |
| 10625 | 60 | 10 |
| product_id | product_name |
|---|---|
| 14 | Tofu |
| 24 | Guaraná Fantástica |
| 42 | Singaporean Hokkien Fried Mee |
| 57 | Ravioli Angelo |
| 60 | Camembert Pierrot |
| 69 | Gudbrandsdalsost |
| 70 | Outback Lager |
Follow the keys for one line. Order 10308 is ANATR's. Its first line in order_details sold product 69, 1 of it. Product 69 is Gudbrandsdalsost. So one row of the answer is 10308 | Gudbrandsdalsost | 1.
| Step | Table | Linked by |
|---|---|---|
| 1 | orders: the customer's orders |
|
| 2 | order_details: each order's lines |
order_id to orders |
| 3 | products: each line's product name |
product_id to order_details |
Your query should give exactly these rows, in this order:
| order_id | product_name | quantity |
|---|---|---|
| 10308 | Gudbrandsdalsost | 1 |
| 10308 | Outback Lager | 5 |
| 10625 | Camembert Pierrot | 10 |
| 10625 | Singaporean Hokkien Fried Mee | 5 |
| 10625 | Tofu | 3 |
Your job: write one SELECT query that joins the three tables.
- Start from
orders, joinorder_detailsonorder_id, then joinproductsonproduct_id. - Show three columns,
order_id,product_nameandquantity, in that order. - Keep only the orders whose
customer_idis'ANATR'. - Sort the rows by
order_id, then byproduct_name, A to Z.
order_id is in two of the tables and product_id in two, so say which table you mean each time you use one of them. Short aliases, as the lesson's AS o, AS od and AS p, keep that readable.
Practiced live on the lesson page: write the SQL right there and check it.