Practise · Follow a loan through its keys

Opening your saved work…

The reading

Follow a loan through its keys

SELECT l.id AS loan_id, m.first_name AS member_first_name, t.name AS tool_name
FROM loans l
JOIN members m ON m.id = l.member_id
JOIN tools t ON t.id = l.tool_id
WHERE l.id <= 5
ORDER BY l.id;

Five loans, each with the member who borrowed and the tool that left. Open in the playground

A loan row names a member, a tool and a staff member by their keys. When each output row should describe a loan, start from loans and follow each foreign key to its parent: JOIN members on l.member_id, JOIN tools on l.tool_id, JOIN staff on l.staff_id. Three joins, one row per loan, 120 rows. The hub is the table whose rows the report is about.

Resist the shortcut. Members and tools both carry a branch_id, so you could pair them on it. That pairs every member with every tool at the same branch, 341 rows that mean nothing: a shared branch is not a loan. For every join you write, name the fact that makes the pair real.

The Tool Library has three different branches in one loan: the branch that owns the tool, the home branch of the member, and the branch where the staff member works. They are three relationships, three joins to the branches table under three aliases. The director's replacement-value report attributes value to the branch that owns the tool. Pick the branch the question is about, not the one that is easiest to reach.

Rule of thumb. Start from the table the report is about and follow each foreign key to its parent.

Follow a loan through its keys · three-table joins, and which branch is which · level 1 of 6

A loan names three things

Level 1: Watch it answered · Exercise 1 of 1

The first three loans with the ids of the member and the tool they connect. Watch the two joins, then run it.

Data: Twin Ports Tool Library.

Why it works, clause by clause

FROM loans l: each output row is a loan, so loans is the hub.
JOIN members m ON m.id = l.member_id: follow the loan's member_id to its member.
JOIN tools t ON t.id = l.tool_id: follow the loan's tool_id to its tool. Each join starts from the hub, not from the previous parent.
ORDER BY l.id LIMIT 3: a repeatable preview.

From the reading: Follow a loan through its keys, paragraph 1

I would like help with this exercise

SELECT l.id AS loan_id, m.id AS member_id, t.id AS tool_id
FROM loans l
JOIN members m ON m.id = l.member_id
JOIN tools t ON t.id = l.tool_id
ORDER BY l.id
LIMIT 3;

Open in the playground

Query results

Run it to see the result here.

Practice here is evidence for you and your instructor, never a grade. Grades live in Canvas.