SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2009
    Location
    Scotland
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with mysql join query

    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. #2
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Code:
    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. #3
    SitePoint Enthusiast
    Join Date
    Feb 2009
    Location
    Scotland
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Interesting solution, however I figured out an easier query
    Code:
    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!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •