I'm working on a database for calculating race times, and I'm struggling to figure out the query I need to find unmatched times.
The first table, entered_times, contains all times that have been entered, and each row has the following value-
time_id - autoincrement
racer_no - integer
time - integer value (we're dealing with milliseconds that mysql can't handle so all times are converted)
timing_point_id - integer (this links to another table that lists details for each timing point)
The second table, time_links, contains two columns-
start_time_id - should correspond to the start time_id for the particular rider on the selected stage
linked_time_id - should correspond to the split/finish time_id for the start_time_id
To create the time_link entries, I'm trying to semi-automate the process by running a query and displaying the suggested start times for the selected racers split/finish time. Each racer could complete the same stage several times, so they could have multiple start/split/finish times for the same timing_point_id's. What I need to do is link each split/finish time to a start time.
To do that I need to select the rows from entered_times for a given racer_no and timing_point_id, where there is no corresponding entry in the time_links table, linking the time_id to another time_id for the same timing_point_id's.
I know I need to use a JOIN of some kind, but if somebody could point me in the right direction I'd be very grateful!