SQLite Forum

Join tables by nearest older date
Login
Given (simplified) tables:

```
create table WinEvents (
    WinID      integer primary key not null,
    WinDate    text   -- format "yyyy-mm-dd"
)

create table GameEvents (
    GameID     integer primary key not null,
    GameDate   text   -- format "yyyy-mm-dd"
)
```

I'd like to select all records in `WinEvents` joined with the most recent record from `GameEvents` where `GameDate <= WinDate`.

If I knew that the dates always matched, it would be a simple join:

```
select *
    from WinEvents
    join GameEvents
    on WinEvents.WinDate = GameEvents.GameDate
```

but unfortunately many of the records in `WinEvents` hold the date that win-events were _recorded_, which can be a few days after the date of the game-event to which they (should) refer. (I didn't choose this structure: I want the above query to help migrate existing data to a better schema).

I've tried a few things of my own devising without luck, and the closest examples I've found on the web all seem to be about finding the most recent record from the several that match through a more typical JOIN based on ID fields.

Thanks in advance for any assistance.