SQL One-to-Many Relationships

One row on the 'one' side can relate to several rows on the 'many' side — by far the most common relationship pattern in real databases.

What & Why

A one-to-many relationship lets one parent row correspond to several child rows while each child points to one parent. One marketing.campaigns row can have many marketing.leads rows, and each lead stores its campaign in campaign_id.

See How It Works

BUSINESS QUESTION

Marketing lists the leads belonging to each campaign.

id	name	channel	spend	start_date	end_date	status	target_segment
1	Spring Launch	google_ads	55000.00	2024-01-15	2024-03-31	active	smb
2	Retention Webinar	email	45000.00	2024-02-10	2024-04-15	active	enterprise
3	Finance Retargeting	linkedin	50000.00	2024-03-12	2024-05-31	active	enterprise
4	Enterprise Search	google_ads	60000.00	2024-04-01	2024-06-30	active	enterprise

id	campaign_id	email	created_at	qualified_at	converted_at	lead_score	source	country
301	1	ana@example.com	2024-01-21 09:10:00+00	2024-01-22 11:00:00+00	2024-02-02 10:00:00+00	86	google_ads	US
302	1	ben@example.com	2024-01-24 12:40:00+00	NULL	NULL	52	google_ads	CA
303	2	chloe@example.com	2024-02-16 08:30:00+00	2024-02-18 14:20:00+00	NULL	74	email	GB
304	3	dev@example.com	2024-03-20 17:15:00+00	2024-03-21 09:00:00+00	2024-04-04 16:00:00+00	91	linkedin	US

Trace the relationship row by row1× speed

marketing.campaigns

id	name
1	Spring Launch
2	Retention Webinar
3	Finance Retargeting
4	Enterprise Search

campaign.id = lead.campaign_id

marketing.leads

id	campaign_id	source
301	1	google_ads
302	1	google_ads
303	2	email
304	3	linkedin

Result set · 1 rows

campaign_name	lead_id	source
Spring Launch	301	google_ads

Lead 301 matches campaign 1.

EXAMPLE QUERY

SELECT
  c.name AS campaign_name,
  l.id AS lead_id,
  l.source
FROM marketing.campaigns c
JOIN marketing.leads l ON l.campaign_id = c.id
ORDER BY campaign_name, lead_id;

Intermediate business practice