Joining tables: putting it together

01 / 03

What you can do now

Lay out the tools you picked up in this chapter.

a query inside a query … subqueries
headings that read … AS
joining two tables … JOIN and ON
joining while keeping one side … LEFT JOIN
joining as many as you like … one JOIN after another

Each one is short on its own. Using them together is the real thing.

02 / 03

The writing order never changes

The clauses come in a fixed order, so when you are stuck, fit what you want into this shape.

SELECT columns FROM table JOIN table ON key WHERE condition GROUP BY what to gather by HAVING condition ORDER BY sort LIMIT count

You can leave out the parts you do not need, but you cannot move them around.

SELECT items.name AS item,  SUM(orders.quantity) AS total  FROM orders  JOIN items  ON orders.item_id = items.id  WHERE orders.channel = 'app'  GROUP BY items.name  ORDER BY total DESC, item;

Result

sunrise mug | 3
cotton dishcloth | 2
cypress chopstick rest | 2
dappled light lamp | 1

03 / 03

Read a question as four questions

However long the wording, this is all it is asking. Jot it down before you write.

which tables do you need? if you want a name or a town, join that table
how do they join? which column is the key
which rows do you keep? gather? sort?
do the headings need aliases?

Only for the "find what is not there" questions, remember LEFT JOIN and IS NULL. Try your hand on the three tables of the handmade-goods shop.