← ACARA Digital Technologies Years 9–10

Answering a question with a multi-table query

A query that gets fields from more than one table: find which tables hold them, JOIN them on matching keys, then add WHERE and ORDER BY to match the question.

Part of: ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 47, QCE Digital Solutions Unit 3 — Interactions Between Users, Data and Digital Systems — Module 117, WA Computer Science ATAR Year 11 — Data Management — Module 81

Understanding Answering a question with a multi-table query in ACARA Digital Technologies Years 9–10

What it does: memberId in loans is the foreign key that points to the primary key memberId in members, so this ON condition matches each loan to its member. The teacher's name is in the teachers table, so the query must join it. Without the JOIN, teachers.name is not available from the classes table. ON links the tables using the keys. WHERE filters the joined rows to match the question, for example WHERE members.firstName = 'Ava'. ORDER BY sorts the output rows. The question asks for the loans to be in date order, so add ORDER BY at the end of the query.

Common mistakes: The join must follow the foreign key. loans.memberId is the foreign key that matches members.memberId, not loans.loanId. The ON line should be ON m.memberId = l.memberId. The foreign key in classes is teacherId, so it must be matched to the primary key teachers.teacherId. classId is the wrong field. It should be ON c.teacherId = t.teacherId.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Answering a question with a multi-table query to a class →

This module's key terms are also in a printable crossword (Modules 459–475) and word search (Modules 459–534).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
A question asks for each member's name and the titles of the books they have on loan. Members are in one table and loans in another. What is the first step?
Work out which tables hold the fields you needWrite ORDER BY before choosing any tables
Matches
SELECT members.name, loans.title FROM members JOIN loans;
↓
The ON condition is missing, so every member is paired with every loan and the results include loans that member never borrowed.
Applying
Meanings
Term
multi-table query
Meaning
A query that gets fields from more than one table: find which tables hold them, JOIN them on matching keys, then add WHERE and ORDER BY to match the question.
Practice
SELECT students.firstName, subjects.name, results.mark
Testing
Gaps
FROM members loans
Check Answer
Spots
2FROM members m JOIN loans l
3 ON m.memberId = l.loanId

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Viperfish' badge — Deep Debugger, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. A question asks for each member's name and the titles of the books they have on loan. Members are in one table and loans in another. What is the first step?
A. Write ORDER BY before choosing any tables
B. Work out which tables hold the fields you need ✓
Start by finding which tables hold the fields the question asks for. Names are in members and loan details are in loans, so the query needs both.
2. Which ON condition correctly joins loans to members?
A. ON loans.memberId = members.memberId ✓
B. ON loans.loanId = members.memberId
Follow the keys. The foreign key memberId in loans must equal the primary key memberId in members. loanId is the key of the loans table itself and does not match a member.
3. Which query lists each class with its teacher's name?
A. SELECT classes.className, teachers.name FROM classes JOIN teachers ON classes.teacherId = teachers.teacherId; ✓
B. SELECT classes.className, teachers.name FROM classes;
The teacher's name is in the teachers table, so the query must join it. Without the JOIN, teachers.name is not available from the classes table.
4. You want only the loans for the member named 'Ava'. Where does that condition go?
A. Inside the ON condition instead of the keys
B. In a WHERE clause after the JOIN ... ON ✓
ON links the tables using the keys. WHERE filters the joined rows to match the question, for example WHERE members.firstName = 'Ava'.
5. A question says: 'list the members' loans in order of due date'. Which clause finishes the query?
A. ORDER BY loans.dueDate ✓
B. GROUP BY loans.dueDate
ORDER BY sorts the output rows. The question asks for the loans to be in date order, so add ORDER BY at the end of the query.

Match the Pairs

SELECT members.name, loans.title FROM members JOIN loans ON members.memberId = loans.memberId; → Lists each member's name beside the titles of the loans they have, by matching the key in both tables.
SELECT classes.className, teachers.name FROM classes JOIN teachers ON classes.teacherId = teachers.teacherId ORDER BY classes.className; → Lists every class with its teacher's name, sorted by class name.
Follow the keys: join the two tables where the foreign key equals the primary key shown on the ERD relationship line. → ON loans.memberId = members.memberId
After joining the tables, add WHERE to keep only the rows the question asks for, such as one member's loans. → WHERE members.name = 'Ava Nguyen'
SELECT members.name, loans.title FROM members JOIN loans; → The ON condition is missing, so every member is paired with every loan and the results include loans that member never borrowed.
FROM classes JOIN teachers ON classes.classId = teachers.teacherId → The join compares the wrong fields: classId is the primary key of classes, not the foreign key that points to teachers.

Key Term

multi-table query
A query that gets fields from more than one table: find which tables hold them, JOIN them on matching keys, then add WHERE and ORDER BY to match the question.

Fill in the Blanks

1. SELECT members.name, loans.title FROM members JOIN loans ON members.id = loans.memberId;
Hint: Type the keyword that combines rows from the two tables.
2. SELECT classes.className, teachers.name FROM classes JOIN teachers ON classes.teacherId = teachers.id;
Hint: Type the foreign key field in classes that points to the teachers table.
3. SELECT m.name, l.title FROM members m JOIN loans l ON m.id = l.memberId WHERE l.returned = 'No' ORDER BY m.name;
Hint: Type the keyword that filters the joined rows to match the question.
4. SELECT c.className, t.name FROM classes c JOIN teachers t ON c.teacherId = t.id WHERE c.yearLevel = 9 ORDER BY t.name;
Hint: Type the clause that sorts the results by the teacher's name.
5. SELECT classes.className, teachers.name FROM classes JOIN teachers ON classes.teacherId = teachers.id;
Hint: Type the table that the ERD links to classes, which holds the name field you need.

Spot the Error

Question 1
A SELECT m.name, l.dueDate
B FROM members m JOIN loans l
C ON m.memberId = l.loanId
D ORDER BY m.name
Line C — The join must follow the foreign key. loans.memberId is the foreign key that matches members.memberId, not loans.loanId. The ON line should be ON m.memberId = l.memberId.
Question 2
A SELECT c.className, t.name
B FROM classes c
C JOIN teachers t
D ON c.classId = t.teacherId
E ORDER BY c.className
Line D — The foreign key in classes is teacherId, so it must be matched to the primary key teachers.teacherId. classId is the wrong field. It should be ON c.teacherId = t.teacherId.
Question 3
A SELECT m.name, l.dueDate
B FROM members m JOIN loans l
C ON m.memberId = memberId
D WHERE m.name = 'Ava'
Line C — Both members and loans have a memberId field, so the second one is ambiguous. Each field in the ON condition needs its table alias, giving ON m.memberId = l.memberId.
Question 4
A SELECT t.name, c.className
B FROM teachers t JOIN classes
C ON t.teacherId = c.teacherId
D ORDER BY t.name
Line B — The query uses the alias c in SELECT and ON, but the classes table was never given that alias. It should be FROM teachers t JOIN classes c.
Question 5
A SELECT m.name, l.title
B FROM members m JOIN loans l
C ON m.memberId = l.memberId
D JOIN books b
E ON l.bookId = b.bookId
F WHERE m.name = 'Ava'
Line A — The title field is held in the books table, not in loans. The query must select b.title, because b is the alias for books.

Student with a class code? Join your class →