Joining three tables

Chain joins across a relationship.

Follow the chain

An order's books live two hops away: orders → order_items → books. Chain the joins:

SELECT o.id, b.title, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN books b ON b.id = oi.book_id;

Each JOIN adds a table and an ON linking it to one already in the query. This is the everyday shape of real reporting queries.

order_items sits between orders and books: its primary key is both columns together, and each of them is also a foreign key (drawn with both underlines). Every JOIN in the query is one of these two arrows.