left join with "soft" condition

[SashaBuilder3]

Hello everybody,

I need to join two tables.The first table has the patient ID and dates of some test.
t1
ID Dt1
1 05-jan-2010
1 14-mar-2011
2 02-feb-2009
2 17-jan-2010


The second has dates of some other test with a few additional parameters.
t2
ID Dt2 Param1 Param2
1 17-nov-2009 1 5
1 12-feb-2010 1
1 27-mar-2010 3 5
1 03-jan-2011 3
1 20-feb-2011 5 5
1 15-apr-2011 2 1
2 12-jan-2009
2 27-feb-2009 7
2 19-mar-2009 3
2 25-dec-2009 3
2 08-mar-2010 1 2


The problem is that the dates may or may not coincide so I need to add the records from the second table based on the closest date:

ID Dt1 Dt2 Param1 Param2
1 05-jan-2010 12-feb-2010 1
1 14-mar-2011 20-feb-2011 5 5
2 02-feb-2009 27-feb-2009 7
2 17-jan-2010 25-dec-2009 3


I am hoping you experts can help me with a right SQL statement. I don't really need the second date to appear in the resultset, this is just to indicate which records should be picked.

Thank you in advance
Alex

 

[JarlH]

One possible solution:

[tt]select * from t1 as tm1 left join t2 as tm2 on tm1.id = tm2.id
and ABS((dt1 - dt2) day(4)) = (select min(ABS((dt1 - dt2) day(4)))
from t1 join t2 on t1.id = t2.id
where t1.id = tm1.id
and t1.dt1 = tm1.dt1)[/tt]

Note that the date/interval arithmetics above is the ANSI/ISO SQL standard way. Many dbms products have their own ways for this.

 

[SashaBuilder3]

Hi JarlH,

Thanks for the solution. I need it for SAS (they have their SQL facility) and I will think how to adopt the statement for it.

Thanks again!
Alex