SQL Multiple JOINs

Chain more than one JOIN in a single query — each new JOIN builds on the combined result of everything before it, not on the original table alone.

What & Why

Each additional JOIN connects another table to the result assembled so far. The Queryflo example starts from saas.accounts, then attaches saas.invoices and saas.seats; because both child tables are one-to-many, their rows can multiply each other before aggregation.

See How It Works

BUSINESS QUESTION

SaaS leadership wants invoice and seat counts beside every account.

id	name	plan	mrr	created_at	churned_at	country	employee_count	industry
1	Acme Labs	growth	240.00	2024-01-08 10:00:00+00	NULL	US	45	software
2	Northstar Co	starter	90.00	2024-02-12 09:30:00+00	2024-05-18 12:00:00+00	CA	18	services
3	Atlas Works	scale	480.00	2024-03-04 15:10:00+00	NULL	GB	120	manufacturing
4	Bright Path	growth	180.00	2024-04-19 11:45:00+00	NULL	US	62	education

id	account_id	amount	status	due_date	paid_at
10	1	240.00	paid	2024-02-01	2024-01-29 13:00:00+00
11	1	260.00	paid	2024-03-01	2024-02-28 16:30:00+00
12	2	90.00	overdue	2024-03-15	NULL
13	3	480.00	open	2024-04-01	NULL

id	account_id	user_email	role	created_at	last_active_at
20	1	owner@acme.test	owner	2024-01-08 10:10:00+00	2024-04-10 08:30:00+00
21	1	analyst@acme.test	member	2024-01-10 14:00:00+00	2024-04-09 16:20:00+00
22	2	owner@northstar.test	owner	2024-02-12 09:40:00+00	2024-04-02 10:00:00+00
23	3	admin@atlas.test	admin	2024-03-04 15:20:00+00	2024-04-11 12:15:00+00

Trace the relationship row by row1× speed

saas.accounts

id	name
1	Acme Labs
2	Northstar Co
3	Atlas Works
4	Bright Path

TWO LEFT JOINS

saas.invoices

id	account_id	amount	status
10	1	240.00	paid
11	1	260.00	paid
12	2	90.00	overdue
13	3	480.00	open

saas.seats

id	account_id	user_email	role
20	1	owner@acme.test	owner
21	1	analyst@acme.test	member
22	2	owner@northstar.test	owner
23	3	admin@atlas.test	admin

Result set · 1 rows

name	invoice_count	seat_count
Acme Labs	2	2

Acme's two invoices and two seats create four intermediate combinations; both DISTINCT counts remain two.

EXAMPLE QUERY

SELECT
  a.name,
  COUNT(DISTINCT i.id) AS invoice_count,
  COUNT(DISTINCT s.id) AS seat_count
FROM saas.accounts a
LEFT JOIN saas.invoices i ON i.account_id = a.id
LEFT JOIN saas.seats s ON s.account_id = a.id
GROUP BY a.id, a.name
ORDER BY a.name;

Now You Try

Practice this concept

SaaS leadership wants invoice and seat counts beside every account.

Available schema

saas

Prefix tables with saas.table_name.

nameinvoice_countseat_count

saas.accounts

Column	Type
id	integer
name	text
plan	text
mrr	numeric
created_at	timestamp with time zone
churned_at	timestamp with time zone
country	text
employee_count	integer
industry	text
customer_segment_v2	text

saas.invoices

Column	Type
id	integer
account_id	integer
amount	numeric
status	text
due_date	date
paid_at	timestamp with time zone
internal_invoice_batch	text

saas.seats

Column	Type
id	integer
account_id	integer
user_email	text
role	text
created_at	timestamp with time zone
last_active_at	timestamp with time zone
internal_seat_group	text

query.sql

Intermediate business practice