×
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!
  • Students Click Here

*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.

Students Click Here

Jobs

left join with "soft" condition

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!

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