Joining three tables
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. You want each student's name with the subject name for each mark. Which FROM clause reaches all three tables?
2. How many JOIN ... ON lines do you need to link three tables in one query?
3. The query already joins students to results. Which ON condition for the second JOIN reaches subjects?
4. Why is results in the middle of students and subjects?
5. You join students, orders and products. Which second join is correct?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1JOIN results ON students.studentId = results.studentId | AFROM students JOIN results ON students.studentId = results.studentId JOIN subjects ON results.subjectId = subjects.subjectId | |
| 2JOIN subjects ON results.subjectId = subjects.subjectId | BThe second JOIN. It follows the foreign key in results to the primary key of subjects to reach the third table. | |
| 3Reaching three tables needs two JOIN lines, one for each link between tables | CSELECT students.firstName, subjects.name, results.mark | |
| 4Once both joins are in place, the SELECT can list fields from the first and third tables, with the junction table in between | DThe first JOIN. It links each student to their rows in the junction table results. | |
| 5JOIN subjects ON students.studentId = subjects.subjectId | EThis 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. | |
| 6FROM students JOIN results ON students.studentId = results.studentId JOIN subjects | FThe second JOIN has no ON condition, so the keys are never matched. It needs ON results.subjectId = subjects.subjectId. |
C
Definitions
JOIN subjects ON results.subjectId = subjects.subjectId
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
FROM students s
JOIN orders o ON s.studentId = o.studentId
____ products p ON o.productId = p.productId
2.
FROM students s
JOIN orders o ON s.studentId = o.studentId
JOIN products p ON o._________ = p.productId
3.
FROM students s
JOIN orders o ON s.studentId = o.studentId
JOIN ________ p ON o.productId = p.productId
4.
FROM students s
JOIN orders o ON s.studentId = o.studentId
JOIN products p ON _.productId = p.productId
5.
FROM students s
JOIN orders o ON s.studentId = o.studentId
JOIN products p __ o.productId = p.productId
E
Spot the Error
Shade in the letter of the line that contains an 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
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
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
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
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
Answer Key
Joining three tables
Section A: Quick Questions
Q1
B — FROM students JOIN results ON students.studentId = results.studentId JOIN subjects ON results.subjectId = subjects.subjectId
Q2
A — Two
Q3
A — ON results.subjectId = subjects.subjectId
Q4
B — It is the junction table: it holds a foreign key to each of the other two tables
Q5
A — JOIN products ON orders.productId = products.productId
Section B: Match the Pairs
Q1
JOIN results ON students.studentId = results.studentId → The first JOIN. It links each student to their rows in the junction table results.
Q2
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.
Q3
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
Q4
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
Q5
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.
Q6
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.
Section C: Definitions
Q1
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.Section D: Fill in the Blanks
Q1
FROM students s
JOIN orders o ON s.studentId = o.studentId
JOIN products p ON o.productId = p.productIdQ2
FROM students s
JOIN orders o ON s.studentId = o.studentId
JOIN products p ON o.productId = p.productIdQ3
FROM students s
JOIN orders o ON s.studentId = o.studentId
JOIN products p ON o.productId = p.productIdQ4
FROM students s
JOIN orders o ON s.studentId = o.studentId
JOIN products p ON o.productId = p.productIdQ5
FROM students s
JOIN orders o ON s.studentId = o.studentId
JOIN products p ON o.productId = p.productIdSection E: Spot the Error
Q1
B — The table is called students, not student. The FROM line must use the real table name, then the alias s.
Q2
C — The junction table is orders, not order. It should be JOIN orders o so the first JOIN can link students to it.
Q3
A — The alias p is never defined in this query. The subjects table has the alias sub, so it should be sub.name.
Q4
C — The keyword is JOIN, not JOINS. It should be JOIN students s.
Q5
A — The columns in a SELECT list must be separated by a comma. It should be SELECT s.firstName, p.name.