1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Inner Join Query - VariableTime

Discussion in 'Databases' started by johnberman, Jun 16, 2021.

  1. #1
    Hi

    I have two tables
    meteor_Events and video_Events and the common fields are Date and Time

    I want to search meteor_events where the date and time match any video events so using this query it works fine

    SELECT
        meteor_events.event_id,
        meteor_events.fname,
        meteor_events.date,
        meteor_events.Time
    FROM
        Video_Events
        INNER JOIN
        meteor_events
        ON
            Video_Events.Date = meteor_events.date AND
            Video_Events.Time = meteor_events.Time
    Code (SQL):
    I now want to refine this and bring back results where the date matches but the time matches if the meteor_events.Time is plus or minus 2 seconds

    ie if the Video_Events.Time is say 00:12:34 bring back results if the meteor_events.Time is between 00:12:32 and 00:12:36

    Pointers appreciated

    Kind Regards
    John Berman
    SEMrush
     
    johnberman, Jun 16, 2021 IP
    SEMrush
  2. JEET

    JEET Notable Member

    Messages:
    3,706
    Likes Received:
    476
    Best Answers:
    19
    Trophy Points:
    235
    #2
    Look into addtime and subtime functions.

    SELECT
    meteor_events.event_id,
    meteor_events.fname,
    meteor_events.date,
    meteor_events.Time
    FROM
    Video_Events
    INNER JOIN
    meteor_events
    ON
    Video_Events.Date = meteor_events.date AND
    ( Video_Events.Time >= subtime(meteor_events.Time, "2") or
    Video_Events.Time <= addtime(meteor_events.Time, "2")
    )
     
    JEET, Jun 28, 2021 IP