Joining three tables

Each JOIN ... ON ... adds one more table. An order links to a customer and to a product, so two joins give a readable order line: who bought what, and how many. Table aliases keep it short: FROM orders o JOIN customers c ON c.id = o.customer_id JOIN products p ON p.id = o.product_id.

Filters and sorting apply to the joined result, so WHERE p.category = 'hardware' or ORDER BY o.ordered_at work as expected.

SELECT c.name, p.name, o.quantity
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN products p ON p.id = o.product_id
WHERE p.category = 'hardware';

Your task

Show one row per order with the order id as order_id, its ordered_at, the customer_id, the customer name as customer, the product_id, the product name as product, the quantity, and the product price, in that order. Both tables have a name column, so the aliases are what make the result readable.

Part of the final project. order_lines is the view of every order with its customer and product spelled out. It is saved in this browser and loaded into Project: the order report when you get there.

SELECT o.id AS order_id, o.ordered_at, o.customer_id, c.name AS customer, o.product_id, p.name AS product, o.quantity, p.price
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN products p ON p.id = o.product_id;