Joining Three Tables

Some questions need more than two tables. Which games were in which order, and when was it placed? The titles are in games, the dates in orders, and what connects them is order_items: each of its rows has an order_id and a game_id. So you join order_items with both of them:

SELECT o.id, o.ordered_at, g.title, i.quantity
FROM order_items AS i
JOIN orders AS o ON i.order_id = o.id
JOIN games AS g ON i.game_id = g.id
ORDER BY o.id, g.title;

Each JOIN adds one table and has its own ON. You can imagine that the database works step by step: the first JOIN pairs each item with its order, which gives rows with the columns of both tables; the second JOIN then adds its game to each of these rows. In the end, each row of the result is one item, with the columns of all three tables.

order_items sits in the middle and connects the other two: an order can have many games, and a game can be in many orders. Such a relation is called many-to-many, and a database keeps it in a table between the two, called a junction table, such as order_items. The table reviews is of the same kind: it connects games with the customers who reviewed them.

And you can go on. Each order has a customer_id, so a fourth table, the customers, joins the orders with one more line:

JOIN customers AS c ON o.customer_id = c.id

Its ON uses o, a table that's already in the query. To find the conditions, follow the foreign keys, as you did by hand in the first lesson: order_items.order_id → orders.id, orders.customer_id → customers.id, order_items.game_id → games.id. Each key you follow is one JOIN, and it's clearest to write the JOINs in the order in which you follow them.

Typical mistake: a column of a table that isn't in the query. If you write c.name in SELECT but forget to join customers, you get the error no such column: c.name. The alias exists only once the table is joined.