Description

Login
Back

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

Put it into practice

  1. Read the sample tables and predict the result before running the query.
  2. Solve the task, compare the returned rows, and explain why the solution works.

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.

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.

Practice