I'm running the following query in Postgres:
SELECT raw_times.*, efforts.id as effort_id, efforts.event_id as event_id, splits.id as split_id
INNER JOIN event_groups ON event_groups.id = raw_times.event_group_id
INNER JOIN events ON events.event_group_id = event_groups.id
INNER JOIN efforts ON efforts.event_id = events.id
INNER JOIN aid_stations ON aid_stations.event_id = events.id
INNER JOIN splits ON splits.id = aid_stations.split_id
WHERE efforts.bib_number::text = raw_times.bib_number
AND splits.parameterized_base_name = raw_times.parameterized_split_name
The idea is to locate matching bib numbers and split names and to return raw_time records with various relation ids populated.
In plain English, the logic works like this: For each raw_time, check the event_group_id. An event_group has many events, and an event has many efforts, and the efforts table has a bib_number column. Bib number is unique within an event_group, but is not unique within the entire efforts table.
So for each raw_time, because we know the event_group_id and the bib_number, we can determine which effort it relates to. Knowing the effort allows us also to know the event (because an effort has an event_id).
An event has many splits through the aid_stations join table. Split name is unique within an event. Because we know the event (determined as described above) and we know the split name (it is a column on the raw_times table) we can determine the split_id.
The query works as expected for records where there is a matching bib number and split name. But for records where either the bib number or split name does not match, the WHERE clause is not satisfied, so the raw_time record is not returned at all.
I've tried the query with LEFT JOIN in place of each INNER JOIN, but I get the same result.
What I'd like is for all raw_time records to be returned, but if there is no matching split name, return the record with NULL for the split_id, and if there is no matching bib number, return the record with NULL for effort_id, event_id, and split_id.
The raw_times table looks like this:
id event_group_id parameterized_split_name bib_number
3 53 finish 11
4 53 finish 603
5 53 finish 9999
6 53 nonexistent 603
The event_groups table looks like this:
The events table looks like this:
The efforts table looks like this:
id event_id bib_number
22183 26 11
22400 28 603
5747 18 11
The aid_stations table looks like this:
id event_id split_id
236 26 30
237 26 31
238 26 106
239 26 111
240 26 112
241 26 109
242 26 113
254 28 119
255 28 118
138 18 1
150 18 16
The splits table looks like this:
The query should return this:
id event_group_id parameterized_split_name bib_number effort_id event_id split_id
3 53 finish 11 22183 26 30
4 53 finish 603 22400 28 119
6 53 nonexistent 603 22400 28 NULL
5 53 finish 9999 NULL NULL NULL
Here's a link to the ERD: https://github.com/SplitTime/OpenSplitTime/blob/master/erd.pdf