Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


left join with "soft" condition

left join with "soft" condition

Hello everybody,

I need to join two tables.The first table has the patient ID and dates of some test.
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.
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

RE: left join with "soft" condition

One possible solution:

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

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

RE: left join with "soft" condition

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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close