SQL join each row in a table with a one row from another table

0
votes

The Problem

I have a table window with start and end timestamps. I have another table activity that has a timestamp. I would like to create a query that:

  • For each row in activity it joins with a single row from window, where the timestamp occurs between start and end, choosing the older window.

Window Table

Start End ISBN
0 10 "ABC"
5 15 "ABC"
20 30 "ABC"
25 35 "ABC"

Activity Table

Timestamp ISBN
7.5 "ABC"
27.5 "ABC"

Desired Result

Start End ISBN Timestamp
0 10 "ABC" 7.5
20 30 "ABC" 27.5

The Attempt

My attempt at solving this so far has ended with the following query:

SELECT
    *
FROM
    test.activity AS a
    JOIN test.`window` AS w ON w.isbn = (
        SELECT
            w1.isbn
        FROM
            test.window as w1
        WHERE a.`timestamp` BETWEEN w1.`start` AND w1.`end`
        ORDER BY w1.`start`
        LIMIT 1
    )

The output of this query is 8 rows.

1
mysql

1 Answers

0
votes

When there is guaranteed to be an older window (i.e. no two Start times are the same for any ISBN)

select
    a.Timestamp,
    a.ISBN,
    w.Start,
    w.End
from
    Activity a
    inner join Window w on a.ISBN = w.ISBN
        and a.Timestamp between w.Start and w.End
        and w.Start = (select min(Start) from Window where ISBN = a.ISBN)

Keeping an index over (ÌSBN, Start, End) on Window (or clustering the entire table that way by defining those three columns as the primary key) helps this query.