← ACARA Digital Technologies Years 9–10

Joining three tables

A second JOIN line chained after the first to reach a third table. It follows a foreign key in the junction table to the primary key of the next table.

Part of: ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 43, QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 75, QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 43, QCE Digital Solutions Unit 3 — Interactions Between Users, Data and Digital Systems — Module 115, WA Computer Science ATAR Year 11 — Data Management — Module 79

Understanding Joining three tables in ACARA Digital Technologies Years 9–10

What it does: This FROM clause has two JOIN ... ON lines, so it links students to results and then results to subjects. Each JOIN adds one more table to the first. Two JOIN lines link three tables. Follow the foreign key subjectId in results to the primary key subjectId in subjects. studentId and subjectId are different keys and must not be matched. orders.productId is the foreign key that points to products.productId. orders.studentId points to students instead. Without an ON condition, the rows are not matched up by keys. You get every possible pairing, which is wrong. studentId appears in more than one table, so qualify it with the table name. Otherwise SQL reports an ambiguous column.

Common mistakes: The table is called students, not student. The FROM line must use the real table name, then the alias s. The junction table is orders, not order. It should be JOIN orders o so the first JOIN can link students to it.

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 Joining three tables 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

You want each student's name with the subject name for each mark. Which FROM clause reaches all three tables?

FROM students JOIN results ON students.studentId = results.studentId JOIN subjects ON results.subjectId = subjects.subjectIdFROM students JOIN results ON students.studentId = results.studentId JOIN subjects

Matches

JOIN subjects ON students.studentId = subjects.subjectId

↓

This ON condition skips the junction table and compares a student ID with a subject ID, which are unrelated keys. It should be results.subjectId = subjects.subjectId.

Applying

Meanings

Term

JOIN subjects ON results.subjectId = subjects.subjectId

Meaning

A second JOIN line chained after the first to reach a third table. It follows a foreign key in the junction table to the primary key of the next table.

Practice

SELECT students.firstName, subjects.name, results.mark

Testing

Gaps

products p ON o.productId = p.productId

Check Answer

Spots

1SELECT s.firstName, sub.name

2FROM student s

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

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. You want each student's name with the subject name for each mark. Which FROM clause reaches all three tables?

A. FROM students JOIN results ON students.studentId = results.studentId JOIN subjects

B. FROM students JOIN results ON students.studentId = results.studentId JOIN subjects ON results.subjectId = subjects.subjectId ✓

Every JOIN needs its own ON condition. The second JOIN follows results.subjectId to subjects.subjectId.

2. How many JOIN ... ON lines do you need to link three tables in one query?

A. Two ✓

B. Three

Each JOIN adds one more table to the first. Two JOIN lines link three tables.

3. The query already joins students to results. Which ON condition for the second JOIN reaches subjects?

A. ON results.subjectId = subjects.subjectId ✓

B. ON students.studentId = subjects.subjectId

Follow the foreign key subjectId in results to the primary key subjectId in subjects. studentId and subjectId are different keys and must not be matched.

4. Why is results in the middle of students and subjects?

A. It is always the biggest table, so SQL needs it first

B. It is the junction table: it holds a foreign key to each of the other two tables ✓

The junction table connects students and subjects (a many-to-many relationship). That is why you go through it to get from one to the other.

5. You join students, orders and products. Which second join is correct?

A. JOIN products ON orders.productId = products.productId ✓

B. JOIN products ON orders.studentId = products.productId

orders.productId is the foreign key that points to products.productId. orders.studentId points to students instead.

Match the Pairs

JOIN results ON students.studentId = results.studentId → The first JOIN. It links each student to their rows in the junction table results.

JOIN subjects ON results.subjectId = subjects.subjectId → The second JOIN. It follows the foreign key in results to the primary key of subjects to reach the third table.

Reaching three tables needs two JOIN lines, one for each link between tables → FROM students JOIN results ON students.studentId = results.studentId JOIN subjects ON results.subjectId = subjects.subjectId

Once both joins are in place, the SELECT can list fields from the first and third tables, with the junction table in between → SELECT students.firstName, subjects.name, results.mark

JOIN subjects ON students.studentId = subjects.subjectId → This ON condition skips the junction table and compares a student ID with a subject ID, which are unrelated keys. It should be results.subjectId = subjects.subjectId.

FROM students JOIN results ON students.studentId = results.studentId JOIN subjects → The second JOIN has no ON condition, so the keys are never matched. It needs ON results.subjectId = subjects.subjectId.

Key Term

JOIN subjects ON results.subjectId = subjects.subjectId

A second JOIN line chained after the first to reach a third table. It follows a foreign key in the junction table to the primary key of the next table.

Fill in the Blanks

1. FROM students s JOIN orders o ON s.studentId = o.studentId JOIN products p ON o.productId = p.productId

Hint: Type the keyword that chains on a third table after the first join line.

2. FROM students s JOIN orders o ON s.studentId = o.studentId JOIN products p ON o.productId = p.productId

Hint: Type the foreign key field in the junction table that points to the third table.

3. FROM students s JOIN orders o ON s.studentId = o.studentId JOIN products p ON o.productId = p.productId

Hint: Type the name of the third table that the second join line reaches.

4. FROM students s JOIN orders o ON s.studentId = o.studentId JOIN products p ON o.productId = p.productId

Hint: Type the alias of the junction table that holds the foreign key.

5. FROM students s JOIN orders o ON s.studentId = o.studentId JOIN products p ON o.productId = p.productId

Hint: Type the keyword that introduces the matching condition for the second join.

Spot the Error

Question 1

A SELECT s.firstName, sub.name

B FROM student s

C JOIN results r

D ON s.studentId = r.studentId

E JOIN subjects sub

F ON r.subjectId = sub.subjectId

Line B — The table is called students, not student. The FROM line must use the real table name, then the alias s.

Question 2

A SELECT s.firstName, p.name

B FROM students s

C JOIN order o

D ON s.studentId = o.studentId

E JOIN products p

F ON o.productId = p.productId

Line C — The junction table is orders, not order. It should be JOIN orders o so the first JOIN can link students to it.

Question 3

A SELECT s.firstName, p.name

B FROM students s

C JOIN results r

D ON s.studentId = r.studentId

E JOIN subjects sub

F ON r.subjectId = sub.subjectId

Line A — The alias p is never defined in this query. The subjects table has the alias sub, so it should be sub.name.

Question 4

A SELECT s.firstName, sub.name

B FROM results r

C JOINS students s

D ON r.studentId = s.studentId

E JOIN subjects sub

F ON r.subjectId = sub.subjectId

Line C — The keyword is JOIN, not JOINS. It should be JOIN students s.

Question 5

A SELECT s.firstName p.name

B FROM orders o

C JOIN students s

D ON o.studentId = s.studentId

E JOIN products p

F ON o.productId = p.productId

Line A — The columns in a SELECT list must be separated by a comma. It should be SELECT s.firstName, p.name.

Student with a class code? Join your class →