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.