SQL Many-to-Many Relationships

Many rows on BOTH sides can relate to many rows on the other — this pattern always needs a third, 'junction' table in between to actually work.

What & Why

A many-to-many relationship lets each row on both sides relate to several rows on the other side. product.feature_usage acts as a bridge: one feature can be used by many users, and one user can use many features.

See How It Works

BUSINESS QUESTION

Product wants the unique user-feature pairs recorded by feature usage.

id	name	description	team	released_at	deprecated_at
1	Activation Checklist	Guides new users through setup	growth	2024-01-20	NULL
2	CSV Export	Exports report data	platform	2024-02-14	NULL
3	Invite Nudges	Prompts workspace collaboration	growth	2024-03-05	NULL
4	Legacy Dashboard	Original reporting surface	analytics	2023-08-10	2024-06-01

id	feature_id	user_id	used_at	session_id
7001	1	101	2024-01-25 12:20:00+00	sess_101a
7002	2	102	2024-02-20 15:45:00+00	sess_102a
7003	1	103	2024-03-18 09:10:00+00	sess_103a
7004	3	104	2024-04-08 17:35:00+00	sess_104a

EXAMPLE QUERY

SELECT DISTINCT
  f.name AS feature_name,
  u.user_id
FROM product.features f
JOIN product.feature_usage u ON u.feature_id = f.id
ORDER BY feature_name, user_id;

RESULT — exact output from the displayed Queryflo rows

feature_name	user_id
Activation Checklist	101
Activation Checklist	103
CSV Export	102
Invite Nudges	104

product.feature_usage supplies the four real feature-to-user bridge rows.

Now You Try

Practice this concept

Product wants the first 100 unique user-feature pairs represented by feature usage.

Available schema

product

Prefix tables with product.table_name.

feature_nameuser_id

product.feature_usage

Column	Type
id	bigint
feature_id	integer
user_id	integer
used_at	timestamp with time zone
session_id	text
deprecated_usage_source	text

product.features

Column	Type
id	integer
name	text
description	text
team	text
released_at	timestamp with time zone
deprecated_at	timestamp with time zone
internal_priority	integer

query.sql

Intermediate business practice