The librarian asks for a report
A librarian wants a list of every member, with how many books each has out. Members with none must still show.
All the data is already in one library system. The job is to choose the right join.
Key words
| Word | What it means |
|---|---|
| Business requirement | what the report must show, in the customer's words |
| Combined dataset | one result made by joining tables |
| Single source | all the tables live in one database |
| Left join | keeps every row of the left table |
| Inner join | keeps only the rows that match in both tables |
| Database | an organised store of data that you can search |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can choose and apply a join so the combined dataset meets a requirement.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Assess the requirement first
Read the requirement and pull out three things: what rows must appear, which columns, and what happens to rows with no match.
That last point chooses the join. "Members with none must still show" means a left join from members.
From requirement to join
| Requirement says | Join to use |
|---|---|
| Only members who have borrowed | Inner join |
| Every member, even with no loans | Left join from members |
| Every book, even never borrowed | Left join from books |
| Every member and every book, matched or not | Full join |
The cells students fill in are left blank in this preview.
Try it: every member and their loans out
select members.name, count(loans.loan_id) as loans_out
from members
left join loans on loans.member_id = members.member_id
and loans.returned is null
group by members.name
order by members.name;
-- Ada and Mia show with 0. An inner join would hide them.
Check
Have a go
Check
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.