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

By

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.

The customers, orders, order_details, products and categories tables, linked by customer_id, order_id, product_id and category_id

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's order_id, and the customer_id of the customer who placed it.
  • order_details: one row per order line, with its order_id, the product_id it sold, and the quantity.
  • products: each product's product_id and product_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, join order_details on order_id, then join products on product_id.
  • Show three columns, order_id, product_name and quantity, in that order.
  • Keep only the orders whose customer_id is 'ANATR'.
  • Sort the rows by order_id, then by product_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.

Cheat sheets