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

The borrowing list that never looked at a loan

Level 5: Repair it · Exercise 1 of 1

A list of what members 1 and 2 borrowed is twenty-two tools long. Between them they have borrowed twelve different tools.

This query claims to list the tools borrowed by members 1 and 2. It runs and pairs them with every tool at their branch. Fix it so it returns member_id and tool_name for the tools those two members actually borrowed, each tool once per member, ordered by member id and then tool name, and say what the original was pairing.

Finish line: 12 rows · columns member_id, tool_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.