Multi-Table Join: Customers, Orders, and Products — SQL

Solving tips

Chain joins one table at a time, always joining the new table on a key already present in the growing result.
Each ON clause connects exactly two tables; the orders table is the hub linking customers to products.
Compute derived values like line totals in the SELECT after the joins have paired the rows.

An order links a customer to a product. Produce a readable line item for each order.

Schema

CREATE TABLE customers (
    id   INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE products (
    id           INT PRIMARY KEY,
    product_name VARCHAR(50) NOT NULL,
    price        NUMERIC(10,2) NOT NULL
);

CREATE TABLE orders (
    id          INT PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    product_id  INT REFERENCES products(id),
    quantity    INT NOT NULL
);

customers

id	name
1	Alice
2	Bob
3	Carol

products

id	product_name	price
100	Keyboard	25.00
200	Mouse	15.00
300	Monitor	150.00

orders

id	customer_id	product_id	quantity
1	1	100	2
2	1	200	1
3	2	300	1
4	3	100	3

Task

Return one row per order with columns customer_name, product_name, quantity, and line_total (equal to quantity * price). Order by customer_name ascending, then product_name ascending.

Expected output

customer_name	product_name	quantity	line_total
Alice	Keyboard	2	50.00
Alice	Mouse	1	15.00
Bob	Monitor	1	150.00
Carol	Keyboard	3	75.00

Approach

Start from orders, the fact table that carries both foreign keys, then join customers on customer_id and products on product_id. With all three rows paired, the line total is a simple quantity * price expression in the SELECT list.

Query

SELECT c.name         AS customer_name,
       p.product_name AS product_name,
       o.quantity     AS quantity,
       o.quantity * p.price AS line_total
FROM orders AS o
INNER JOIN customers AS c
       ON o.customer_id = c.id
INNER JOIN products AS p
       ON o.product_id = p.id
ORDER BY c.name, p.product_name;

Walkthrough

Order 1: customer 1 (Alice), product 100 (Keyboard, 25.00), quantity 2 gives line_total 50.00.
Order 2: Alice, product 200 (Mouse, 15.00), quantity 1 gives 15.00.
Order 3: customer 2 (Bob), product 300 (Monitor, 150.00), quantity 1 gives 150.00.
Order 4: customer 3 (Carol), product 100 (Keyboard, 25.00), quantity 3 gives 75.00.
ORDER BY c.name, p.product_name sorts Alice’s two rows (Keyboard before Mouse), then Bob, then Carol.

Complexity & notes

Each join resolves through a primary key on the dimension side; indexes on orders.customer_id and orders.product_id help when the fact table is large.
The order of the joined tables does not change the result here because all joins are INNER; the optimizer is free to reorder them. It would matter if any join were an outer join.
Pitfall: if an order could reference a missing customer or product and you still want the order shown, switch that join to LEFT JOIN; with INNER joins any unmatched order silently disappears.