>I want to JOIN one table with another one. The fields should be joined by date. Instead of the exact date, I want to find a record, in the second table, whose date is closest (<=) to a certain date in the first table. In other words, an exact match may, or may not, exist.
>
>Can this be done with SQL - SELECT syntax? I was thinking about the workaround of writing a function that gets the PK of the second table, and then use that field for the join. However, I was wondering whether there was a simpler way to do this.
>
Here's (just) an idea: do a select first to find the distance in days between first and second table, something like
select abs(dateinfirst-dateinsecond) as nDist, ;
firsttable.key, secondtable.key ;
where (the rest of the join condition)
And then pull the records which have the minimal nDist based on this.