How do I join three tables without getting the row counts wrong?
You join them one at a time, and you keep count of the rows at every step. That is the whole answer.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
CREATE TABLE order_lines (
id INTEGER PRIMARY KEY,
order_id INTEGER NOT NULL,
product VARCHAR(120) NOT NULL,
quantity INTEGER NOT NULL
);
INSERT INTO customers (id, name) VALUES
(1, 'Ada Lovelace'),
(2, 'Grace Hopper');
INSERT INTO orders (id, customer_id, amount) VALUES
(1, 1, 370.00),
(2, 2, 900.00);
INSERT INTO order_lines (id, order_id, product, quantity) VALUES
(1, 1, 'Notebook', 2),
(2, 1, 'Pen', 1),
(3, 2, 'Desk lamp', 1);
-- Three tables, joined one after another.
SELECT
c.name AS customer_name,
o.id AS order_id,
l.product,
l.quantity,
o.amount AS order_total
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_lines l ON l.order_id = o.id
ORDER BY c.name, o.id, l.id;
customer_name order_id product quantity order_total
Ada Lovelace 1 Notebook 2 370.00
Ada Lovelace 1 Pen 1 370.00
Grace Hopper 2 Desk lamp 1 900.00
So how do you avoid getting the row count wrong? You count as you go. Two customers joined to two orders gives three rows, because one customer has two orders. Those three rows joined to three order lines gives four rows, because Ada's two orders have three lines between them.
The mistake that people make is to expect the answer to have one row per order, or one row per customer. It has one row per matching combination, and that number grows every time you add a table with many rows per row of the earlier one.
There are two ways to keep it under control. The first one is to join the table with the fewest rows first, so that the intermediate result stays small. The second one is to check the count at every step while you are writing the query, so that you know where the rows multiplied.
So the rule to remember is this. Every join multiplies the rows, so count them as you go and join the smallest tables first.