An order item knows its product by id. The product knows its category by id. To show an item with its product name and category name, follow both links:

SELECT oi.order_id, p.name AS product, c.name AS category
FROM order_items oi
JOIN products p ON p.id = oi.product_id
JOIN categories c ON c.id = p.category_id
WHERE oi.order_id = 101;

• Each JOIN adds one table and has its own ON.
• Each join works on the rows built so far. The second ON can use columns of both earlier tables.
• The joins follow the foreign keys. Draw the chain first, then write one join for each arrow.

Inner joins drop rows at every step

Order 109 holds the Mystery Box, which has no category. In the query above without the WHERE, the second inner join drops that item. To keep it, make that join a LEFT JOIN. A later inner join on a column that is NULL drops the row again, so keep the joins after it as LEFT JOIN too.