Three-table join

Real questions often need three tables, such as tests, their defects and the vehicle behind them. Each join can change how many rows there are, so check the counts.

How it works

A join can multiply rows. A test with three defects becomes three rows after it joins to fact_defect, and every column of that test repeats on each of them. COUNT(DISTINCT mot_test_id) counts each test once again.

Common mistake

Counting joined rows instead of distinct tests. A test with three dangerous defects then counts three times, and the rate looks worse than it is.

Worked example

SELECT COUNT(*) AS joined_rows, COUNT(DISTINCT m.mot_test_id) AS tests
FROM `learn.mot.fact_mot_test` AS m
LEFT JOIN `learn.mot.fact_defect` AS fd
  ON m.mot_test_id = fd.mot_test_id
  AND fd.completed_date = '2024-03-12'
WHERE m.completed_date = '2024-03-12'

Every test stays, and each defect adds one more row for its test.

joined_rows is at least tests, and it is larger when some tests have several defects.

All BigQuery lessons