Home SQL query that returns records with certain NULL values when WHERE does not match
Reply: 0

SQL query that returns records with certain NULL values when WHERE does not match

user3929
1#
user3929 Published in April 26, 2018, 1:43 pm

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 
FROM raw_times 
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:

id
53
51

The events table looks like this:

id  event_group_id
26  53
28  53
18  51

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:

id  parameterized_base_name
30  finish
31  start
106 aid-1
109 aid-4
111 aid-2
112 aid-3
113 aid-5
118 start
119 finish
1   start
16  finish

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

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.339954 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO