Joining three tables

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.