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 3 of 6

Members and tools at the same branch

Level 3: Predict the result · Exercise 1 of 1

There are 40 members, 45 tools and 120 loans. Someone pairs members with tools on branch_id to list what each member borrowed. Before you run it, how many rows come back?

SELECT COUNT(*) AS rows_returned
FROM members m
JOIN tools t ON t.branch_id = m.branch_id;

The query. Predict before you run it.

Data: Twin Ports Tool Library.

I would like help with this exercise

Opens once your prediction is right, or after the worked answer.

Query results

The real result appears here after you run it.

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