Sql Join Tables On Time Between Start And End

I have two tables: Events (ID, Name, Time, Recorder) Video (ID, StartTime, EndTime, Recorder, Filename) I wish to join the Event data to the video data, so that for every event I

Solution 1:

It's a little clunky, but here's what I came up with:

SELECT*FROM
    (
        SELECT
            a.ID AS EventID,
            b.ID AS VideoID,
            b.Filename,
            (
                CASEWHEN a.Time < b.StartTime THEN UNIX_TIMESTAMP(b.StartTime) - UNIX_TIMESTAMP(a.Time)
                    WHEN a.Time > b.EndTime THEN UNIX_TIMESTAMP(a.Time) - UNIX_TIMESTAMP(b.EndTime)
                END
            ) AS distance_factor
        FROM
            `Events` a
        CROSSJOIN
            video b
        WHERENOTEXISTS
            (
                SELECTNULLFROM Video
                WHERE a.Time BETWEEN StartTime AND EndTime
            )
    ) c
WHERE 
    c.distance_factor = 
    (
        SELECTMIN(CASEWHEN d.Time < e.StartTime THEN UNIX_TIMESTAMP(e.StartTime) - UNIX_TIMESTAMP(d.Time) WHEN d.Time > e.EndTime THEN UNIX_TIMESTAMP(d.Time) - UNIX_TIMESTAMP(e.EndTime) END)
        FROM
            `Events` d
        CROSSJOIN
            video e
        WHERE d.ID = c.EventID
    )
GROUPBY
    c.EventID

This returns events whose dates don't fall between any of the time ranges of any video, but then returns the video that falls the closest to that event date.

The only thing right now is that there are some videos where the seconds difference is exactly the same. I don't know if you want it to return 2 rows, but for now, I put in the GROUP BY to just select one.

Let me know how that works.

Solution 2:

My final result was:

SELECT*FROM
    (SELECT*FROM
        (SELECT*FROM
            (SELECT*, (CASEWHENTime< StartTime THEN UNIX_TIMESTAMP(StartTime) - UNIX_TIMESTAMP(Time)
                    WHENTime> EndTime THEN UNIX_TIMESTAMP(Time) - UNIX_TIMESTAMP(EndTime)
                END
                ) AS SecondsDifference 

            FROM
            (
                SELECT*FROM Events E
                    LEFTJOIN Video V ON (E.Time >= V.StartTime AND E.Time <= V.EndTime)
                    WHERE DVID ISNULLGROUPBY E.EventID
            ) A ORDERBY A.EventID, A.SecondsDifference
    ) B GROUPBY EventID
) C WHERE C.SecondsDifference ISNOTNULL

Essentially this first gets all events without any video, then joins this result on the entire video list, orders it by the EventID and ClosestSeconds, and then Groups the result by the EventID to remove the duplicates. Finally, I needed to remove any Events where the SecondsDifference was null.

It produces the same result as Zane's answer.

Thanks a lot Zane.