MySQL join two tables based on a time field returning the largest previous time

I have two tables like so:

EventTable (EID, Name, EventTime)
VideoTable (VID, StartTime, Video, DurationSecs)

The EventTable contains events that occur at a particular time. The VideoTable contains a list of video files and their starting times.

What I would like to return is a list of all events and the correct video file (and hopefully time into the video file) for each event.

Consider the following data:

EventTable
1, EV1, 2010-01-01 12:00:00
2, EV2, 2010-01-01 12:15:00
3, EV3, 2010-01-01 12:30:00

VideoTable
1, 2010-01-01 11:30:00, A.mpg, 2700
2, 2010-01-01 12:15:00, B.mpg, 2700
3, 2010-01-01 13:00:0开发者_如何学Python0, C.mpg, 1800

The return list would be as follows:

EV1, A.mpg, 1800 (secs)
EV2, B.mpg, 0 (secs)
EV3, B.mpg, 900 (secs)

How could i acheive this?

EDIT

The Time columns are DATETIME types. There is no relationship between EID and VID. The duration column specifies the video duration in seconds.

---

SELECT
  e.Name,
  v.Video,
  TO_SECONDS(v.StartTime) - TO_SECONDS(e.EventTime) AS SecsIntoVideo
FROM EventTable e
  LEFT JOIN VideoTable v
    ON e.EventTime BETWEEN v.StartTime AND v.starttime + INTERVAL (v.DurationSecs)

---

Assuming the event and start time columns are DATETIME (DATE would work too, but you have time declared in the example):

SELECT e.name, v.video,
       ABS(TO_SECONDS(v.starttime) - TO_SECONDS(e.eventtime))
  FROM EVENT e
  JOIN VIDEO v ON v.vid = e.eid

Reference

• TO_SECONDS

---

This would be super simple if you had 2 time columns in the VideoTable (for start and end). That's how time problems like this are usually done (cf. Snodgrass, Celko). Then you do something like:

SELECT e.name, v.vido, DATEDIFF(v.starttime, e.eventtime)
  FROM Event e, Video v
 WHERE e.eventtime BETWEEN v.startTime AND v.endTime

Barring that, you would have to do some other machinations. Adding the end times would be pretty easy.