How can I code this Interesting Condition Join SQL -

In short, he is that I want to do:

Rows do, then join Complete is not present, but use a different value to fill empty columns in a row.

Here's mine:

  Release hsplit.row_date = on CMS_ECH.dbo.hsplit hsplit ANDREWSTABLE.SegStart_Date and hsplit.split = ANDREWSTABLE.dispsplit and hsplit.starttime = ANDREWSTABLE.Interval and hsplit.acd = ANDREWSTABLE.acd1  

Where ACD1 is either 1 or 4.

Some rows will be taped.

As a result of if includes a redundant row_date ... and here it is where my knowledge fails me ... so this description is no other word for it ... free Join the rows using acd1 = 1 then 4 in the table using a different value for ACD1. If ACD is 1-4 then use 1.

I'm using MS SQL 2005

It will happen, it Will not perform brilliantly as the CASE statement will interfere with the index choice, although it is expected to go and find the best.

  select COALESCE (hsplit.requiredfield, hsplit2.requiredfield) Join CMS_ECH.dbo.hsplit hsplit as required fields, ... left HSplit.row_date = ANDREWSTABLE. ExitState_Date and hsplit2.split = ANDREWSTABLE.dispsplit and hsplit2; exitstate and hsplit.plit = ANDREWSTABLE.dispsplit and hsplit.starttime = ANDREWSTABLE.Interval and hsplit.acd = ANDREWSTABLE.acd1 Skip CMS_ECH.dbo.hsplit hsplit2 at hsplit2.row_date = ANDREWSTABLE.SegStart_Date .starttime = in ANDREWSTABLE.Interval case when hsplit2.acd = 1 tHEN 4 when hsplit2.acd = 4 and 1 when ... finally = ANDREWSTABLE.acd1