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

The tool the loan points at

Level 2: Fill the blank · Exercise 1 of 1

Loans 1 to 5 with the member's first name and the tool's name. The second join is missing the column it should follow.

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

Data: Twin Ports Tool Library.

I would like help with this exercise

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 = WHERE l.id <= 5 ORDER BY l.id;

Blank 1 of 1: the loan's column that points at a tool

Open in the playground

Query results

Check to see the result here.

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