SQL · Intermediate · 18 min
Joining several tables
Follow a relationship chain without multiplying unrelated rows.
Sample database
CREATE TABLE groups (id INTEGER PRIMARY KEY, label TEXT NOT NULL);
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, group_id INTEGER REFERENCES groups(id));
CREATE TABLE sessions (id INTEGER PRIMARY KEY, member_id INTEGER NOT NULL REFERENCES members(id), topic TEXT NOT NULL, minutes INTEGER NOT NULL CHECK(minutes > 0), completed INTEGER NOT NULL CHECK(completed IN (0, 1)), day TEXT NOT NULL);
INSERT INTO groups VALUES (1, 'Morning'), (2, 'Evening');
INSERT INTO members VALUES (1, 'Ada', 'Oslo', 1), (2, 'Bo', 'Rome', 1), (3, 'Cy', NULL, 2), (4, 'Dee', 'Oslo', 2);
INSERT INTO sessions VALUES
(1, 1, 'Travel', 20, 1, '2026-01-01'),
(2, 1, 'Food', 10, 0, '2026-01-02'),
(3, 2, 'Travel', 30, 1, '2026-01-01'),
(4, 2, 'Music', 15, 1, '2026-01-03'),
(5, 3, 'Music', 25, 0, '2026-01-02'),
(6, 3, 'Travel', 15, 1, '2026-01-04');Follow each relationship
Each join needs its own relationship condition. Here a session belongs to a member and that member belongs to a group. Qualify columns with table aliases whenever names might be ambiguous.
SELECT g.label, m.name, s.topic FROM groups AS g JOIN members AS m ON m.group_id = g.id JOIN sessions AS s ON s.member_id = m.id ORDER BY s.id;Output
label | name | topic Morning | Ada | Travel Morning | Ada | Food Morning | Bo | Travel Morning | Bo | Music Evening | Cy | Music Evening | Cy | Travel
Check the aggregation grain
Before adding totals, decide what one input row represents. This join produces one row per session. Joining another one-to-many table could duplicate those rows and inflate totals; pre-aggregate when needed.
SELECT g.label, SUM(s.minutes) AS minutes FROM groups AS g JOIN members AS m ON m.group_id = g.id JOIN sessions AS s ON s.member_id = m.id GROUP BY g.id, g.label ORDER BY g.id;Output
label | minutes Morning | 75 Evening | 40
Try it yourself
Code runs on this device. When you are signed in, drafts sync to your account.
Run queries on a fresh sample database. Table changes last for this run only; your query draft is saved separately. Results show column names followed by rows. NULL means a missing value.
Apply what you learned
Return total session minutes per group, following the group-to-member-to-session relationships.
The joined rows represent sessions, so sum their durations.
Show solution
SELECT g.label, SUM(s.minutes) AS minutes FROM groups AS g JOIN members AS m ON m.group_id = g.id JOIN sessions AS s ON s.member_id = m.id GROUP BY g.id, g.label ORDER BY g.id;Each session contributes once to its member group total.
Put it into practice
- Read the sample tables and predict the result before running the query.
- Solve the task, compare the returned rows, and explain why the solution works.