Help with mysql join query

Databases
1

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!

2

Have a look at using user session variables. Here is an example of what you are looking to achieve:

The @diff column can be ignored as this is just being assigned the latest starttime converted to seconds. The diff column on the other hand contains the difference in seconds.


mysql> select * from time_links order by starttime;
+----+-----------+
| id | starttime |
+----+-----------+
|  5 | 09:00:30  | 
|  7 | 09:01:00  | 
|  8 | 09:01:01  | 
|  6 | 09:01:59  | 
|  2 | 09:09:01  | 
|  3 | 09:09:30  | 
|  4 | 09:09:59  | 
|  1 | 17:05:23  | 
+----+-----------+
8 rows in set (0.00 sec)

mysql> SELECT id,
    ->        Sec_to_time(@diff)                               AS starttime,
    ->        starttime                                        endtime,
    ->        IF(@diff = 0, 0, Time_to_sec(starttime) - @diff) AS diff,
    ->        @diff := Time_to_sec(starttime)
    -> FROM   time_links,
    ->        (SELECT @diff := 0) AS x
    -> ORDER  BY time_links.starttime;  
+----+-----------+----------+-------+---------------------------------+
| id | starttime | endtime  | diff  | @diff := Time_to_sec(starttime) |
+----+-----------+----------+-------+---------------------------------+
|  5 | 00:00:00  | 09:00:30 |     0 |                           32430 | 
|  7 | 09:00:30  | 09:01:00 |    30 |                           32460 | 
|  8 | 09:01:00  | 09:01:01 |     1 |                           32461 | 
|  6 | 09:01:01  | 09:01:59 |    58 |                           32519 | 
|  2 | 09:01:59  | 09:09:01 |   422 |                           32941 | 
|  3 | 09:09:01  | 09:09:30 |    29 |                           32970 | 
|  4 | 09:09:30  | 09:09:59 |    29 |                           32999 | 
|  1 | 09:09:59  | 17:05:23 | 28524 |                           61523 | 
+----+-----------+----------+-------+---------------------------------+
8 rows in set (0.00 sec)
3

Interesting solution, however I figured out an easier query

SELECT entered_times.time_id FROM time_links, entered_times WHERE entered_times.racer_num = 58
AND entered_times.timing_point_id = 3
AND NOT time_links.start_time_id = entered_times.time_id

Once I’ve got that resultset, I can then use it to calculate various times in php, to see if there are any plausible times. I need this working for the weekend, so I’m going for the quick and dirty options for now, and will refine code later!