Name the Tool

Why the data is in pieces

Look at the three tables in the editor. members holds people, tools holds things, and loans holds events — and a loan stores only the id of the member and the id of the tool, not their names.

That is deliberate. Write a member's name on every loan and you have the same fact in a dozen places, which means a dozen places to change when they correct the spelling, and a dozen chances to miss one. Storing the id instead keeps each fact in one place. A column that holds another table's id like this is called a foreign key.

The cost is that reading the data now takes two tables at once, and that is what a join is for.

Reading an ON clause

A join takes every possible pairing of rows from the two tables and keeps the ones where the ON condition is true.

CREATE TABLE keepers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE hives (id INTEGER PRIMARY KEY, label TEXT, keeper_id INTEGER);
INSERT INTO keepers (id, name) VALUES (1, 'Asha'), (2, 'Bruno');
INSERT INTO hives (label, keeper_id) VALUES ('north hedge', 1), ('orchard', 1), ('lane end', 2);

SELECT k.name AS keeper, h.label AS hive
FROM hives AS h
JOIN keepers AS k ON k.id = h.keeper_id
ORDER BY k.name, h.label;

keeper | hive
-------+------------
Asha   | north hedge
Asha   | orchard
Bruno  | lane end
(3 rows)

FROM hives AS h gives every row the short name h; JOIN keepers AS k does the same for the other table. ON k.id = h.keeper_id is the rule: keep the pair where the keeper's own id equals the id the hive is pointing at.

Notice that Asha appears twice. A join does not add columns to a row — it builds pairs, so a keeper with two hives produces two rows. That is worth remembering before you total anything up.

Which table to start from

Starting the FROM at the table your output rows are about usually reads best. This query is about hives, so it starts there and joins the keeper on. The exercise below is about loans, so it starts at loans and joins outwards to both the member and the tool.

Joining a third table is nothing new — another JOIN ... ON line:

CREATE TABLE keepers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE hives (id INTEGER PRIMARY KEY, label TEXT);
CREATE TABLE checks (id INTEGER PRIMARY KEY, keeper_id INTEGER, hive_id INTEGER, checked_on TEXT);
INSERT INTO keepers (id, name) VALUES (1, 'Asha'), (2, 'Bruno');
INSERT INTO hives (id, label) VALUES (10, 'north hedge'), (11, 'orchard');
INSERT INTO checks (keeper_id, hive_id, checked_on) VALUES
  (1, 10, '2026-04-02'), (2, 11, '2026-04-05'), (1, 11, '2026-04-09');

SELECT k.name AS keeper, h.label AS hive, c.checked_on
FROM checks AS c
JOIN keepers AS k ON k.id = c.keeper_id
JOIN hives   AS h ON h.id = c.hive_id
ORDER BY c.checked_on;

keeper | hive        | checked_on
-------+-------------+-----------
Asha   | north hedge | 2026-04-02
Bruno  | orchard     | 2026-04-05
Asha   | orchard     | 2026-04-09
(3 rows)

Both keepers and hives would be fine with a bare name-style column here because their columns happen to be named differently. In the exercise they are not — both members and tools have a name — so the k. and t. prefixes stop being good manners and become required.

Your turn

The editor holds three tables and a query that half works: it shows who borrowed something and when, but the tool is a bare number because tools has never been joined in.

Press Run first and read the output. Five loans, with tool_id values of 10 to 12 where a name belongs.

Make two changes:

1. Add a second JOIN line that brings in tools AS t, matching t.id against l.tool_id.
2. Replace l.tool_id in the SELECT list with t.name AS tool, t.shelf.

Run it again and you should get five readable rows, oldest first.

Then try changing the new JOIN to JOIN tools AS t ON t.id = l.id and read the result: an empty table, no error. The loan ids run 1 to 5 and the tool ids start at 10, so that condition matches nothing at all. Now renumber the tools 1 to 4 in the INSERT and run the same wrong join again: four rows come back, three of them naming a tool the loan has nothing to do with, and nothing in the output says so. That is the version that reaches a report and gets believed.

If something goes wrong

If you get ambiguous column name: name, a bare name is in the SELECT list and needs a m. or t. in front of it.

If you get twenty rows, the new table was added to FROM with a comma and no ON clause, which pairs every loan with every tool.

If you get no such column: t.name, the alias is missing from the join line — it needs to read JOIN tools AS t.

If the tile cutter never shows up, that is correct: nobody has borrowed it, and an inner join has nothing to pair it with.

Nothing here can break. All three tables are rebuilt from scratch on every run.