INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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.

Jobs

left join with "soft" condition

left join with "soft" condition

(OP)
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

RE: left join with "soft" condition

One possible solution:

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)


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

(OP)
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

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!

Resources

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