Chains of joins
Real questions often span three or more tables. Take a school database:
students(id, name)courses(id, title)enrollments(student_id, course_id, grade)links the two, since each row says that a given student takes a given course and holds a given grade.
That middle table exists because a student can take many courses and a course can hold many students. Neither side can store the relationship in a single column, so it gets a table of its own, usually called a join table or a link table.
Printing names next to course titles means joining twice:
SELECT students.name, courses.title, enrollments.grade FROM enrollments JOIN students ON students.id = enrollments.student_id JOIN courses ON courses.id = enrollments.course_id ORDER BY students.name, courses.title;
Each JOIN adds one table and one ON rule. Read it as a pipeline: start from enrollments, attach the matching student, then attach the matching course. There is no limit to the chain, and five-table joins are everyday SQL.
Names, titles, and grades together
Each enrollment row becomes one readable line: who, which course, and what grade.
CREATE TABLE students ( id INTEGER, name TEXT ); CREATE TABLE courses ( id INTEGER, title TEXT ); CREATE TABLE enrollments ( student_id INTEGER, course_id INTEGER, grade INTEGER ); INSERT INTO students VALUES (1, 'Ana'); INSERT INTO students VALUES (2, 'Ben'); INSERT INTO students VALUES (3, 'Cara'); INSERT INTO courses VALUES (10, 'SQL'); INSERT INTO courses VALUES (11, 'Python'); INSERT INTO courses VALUES (12, 'Networks'); INSERT INTO enrollments VALUES (1, 10, 91); INSERT INTO enrollments VALUES (1, 11, 84); INSERT INTO enrollments VALUES (2, 10, 78); INSERT INTO enrollments VALUES (2, 12, 88); INSERT INTO enrollments VALUES (3, 12, 95); SELECT students.name, courses.title, enrollments.grade FROM enrollments JOIN students ON students.id = enrollments.student_id JOIN courses ON courses.id = enrollments.course_id ORDER BY students.name, courses.title;
Output
Ana|Python|84 Ana|SQL|91 Ben|Networks|88 Ben|SQL|78 Cara|Networks|95
Five enrollments produce five output rows, and the ids that made it all work never appear in the result. Every number in the enrollments table has been translated into a name or a title.
The second ON clause can refer to enrollments because that table is already part of the result by the time the third table is attached. Each join in a chain has the whole accumulated result available to match against.
Choosing the starting table
The query starts FROM enrollments because that is the table holding both foreign keys, so each of its rows can reach a student and a course.
Any starting point can be made to work, and the same five rows come back if the query begins from students instead. Starting from enrollments is simply the natural reading: one enrollment row becomes one line of output, which matches the question being asked.
| Table | Foreign keys it carries |
|---|---|
students | none |
courses | none |
enrollments | student_id, course_id |
A good habit is to start from whichever table defines one row of the answer. If the report is one line per enrollment, begin at enrollments, and if it is one line per student, begin at students.
Average grade per course
Only two of the three tables are needed here, since the report never mentions a student.
CREATE TABLE students ( id INTEGER, name TEXT ); CREATE TABLE courses ( id INTEGER, title TEXT ); CREATE TABLE enrollments ( student_id INTEGER, course_id INTEGER, grade INTEGER ); INSERT INTO students VALUES (1, 'Ana'); INSERT INTO students VALUES (2, 'Ben'); INSERT INTO students VALUES (3, 'Cara'); INSERT INTO courses VALUES (10, 'SQL'); INSERT INTO courses VALUES (11, 'Python'); INSERT INTO courses VALUES (12, 'Networks'); INSERT INTO enrollments VALUES (1, 10, 91); INSERT INTO enrollments VALUES (1, 11, 84); INSERT INTO enrollments VALUES (2, 10, 78); INSERT INTO enrollments VALUES (2, 12, 88); INSERT INTO enrollments VALUES (3, 12, 95); SELECT courses.title, ROUND(AVG(enrollments.grade), 1) AS avg_grade FROM enrollments JOIN courses ON courses.id = enrollments.course_id GROUP BY courses.title ORDER BY courses.title;
Output
Networks|91.5 Python|84.0 SQL|84.5
This is the join plus GROUP BY shape from lesson 5-3, with ROUND(AVG(...), 1) for one decimal place as in lesson 4-1. Networks holds grades of 88 and 95, which average to 91.5.
Joining students as well would not have changed a single number, because each enrollment still matches exactly one student. It would only have made the query slower for no benefit, which is a habit worth avoiding: join the tables the answer needs and no more.
One student's courses and grades
The three-table join stays exactly as it was, with a WHERE clause added on the student name.
CREATE TABLE students ( id INTEGER, name TEXT ); CREATE TABLE courses ( id INTEGER, title TEXT ); CREATE TABLE enrollments ( student_id INTEGER, course_id INTEGER, grade INTEGER ); INSERT INTO students VALUES (1, 'Ana'); INSERT INTO students VALUES (2, 'Ben'); INSERT INTO students VALUES (3, 'Cara'); INSERT INTO courses VALUES (10, 'SQL'); INSERT INTO courses VALUES (11, 'Python'); INSERT INTO courses VALUES (12, 'Networks'); INSERT INTO enrollments VALUES (1, 10, 91); INSERT INTO enrollments VALUES (1, 11, 84); INSERT INTO enrollments VALUES (2, 10, 78); INSERT INTO enrollments VALUES (2, 12, 88); INSERT INTO enrollments VALUES (3, 12, 95); SELECT courses.title, enrollments.grade FROM enrollments JOIN students ON students.id = enrollments.student_id JOIN courses ON courses.id = enrollments.course_id WHERE students.name = 'Ben' ORDER BY courses.title;
Output
Networks|88 SQL|78
The WHERE goes before ORDER BY, and it filters on students.name, a column from a joined table, exactly as lesson 5-3 filtered orders on customers.city.
The name is not in the output at all, which is normal. A column can be used for filtering without being selected, and here the caller already knows whose transcript they asked for.