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

Who borrowed, what, and who checked it out

Level 4: Write it yourself · Exercise 1 of 1

For loans 1 to 5, return loan_id, member_first_name, tool_name and staff_last_name (the staff member who checked the loan out), ordered by loan id.

Finish line: 5 rows · columns loan_id, member_first_name, tool_name, staff_last_name · in the order the question gives

Data: Twin Ports Tool Library.

I would like help with this exercise

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Open in the playground

Query results

Run your query to see the result here.

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