Description

Login
Back

Intermediate · 18 min

Joining related tables

Connect records through primary and foreign keys.

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');

Match related rows

An INNER JOIN returns matching row pairs. A member with several sessions appears several times. Use the declared relationship between the foreign key and primary key, not a coincidental match between unrelated IDs.

SELECT m.name, s.topic FROM members AS m JOIN sessions AS s ON s.member_id = m.id ORDER BY s.id;

Output

name | topic
Ada | Travel
Ada | Food
Bo | Travel
Bo | Music
Cy | Music
Cy | Travel

Aggregate after joining

A join can increase row counts before aggregation. Group by the stable member key as well as the displayed name so two members with the same name would remain separate.

SELECT m.id, m.name, SUM(s.minutes) AS minutes FROM members AS m JOIN sessions AS s ON s.member_id = m.id GROUP BY m.id, m.name ORDER BY m.id;

Output

id | name | minutes
1 | Ada | 30
2 | Bo | 45
3 | Cy | 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 member names and session topics by joining sessions through member_id, ordered by session ID.

Show solution
SELECT m.name, s.topic FROM members AS m JOIN sessions AS s ON s.member_id = m.id ORDER BY s.id;

Each session is paired with its member, including all sessions for members with repeated activity.

Practice