How can I code this Interesting Condition Join SQL

Question

In a nutshell here is what I want to happen:

Complete the join, if the rows do not exist do the same join but using one different value to fill the null columns in each row.

Here is my join:

Left Join CMS_ECH.dbo.hsplit hsplit on hsplit.row_date = 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.

For some rows there will be nulls.

If the result of the join returns a null row_date then... and here is where my knowledge fails me... so for the sake of no other words to describe it... rejoin the null rows in the table using a different value for acd1. If acd1 = 1 then use 4. If acd1 is 4 then use 1.

I am using MS SQL 2005

Answer 1

Most (if not all) SQL dialects have an ISNULL() or COALESCE() function, which allows you to specify an alternative value when underlying column is NULL.

Answer 2

This'll do it, it won't perform brilliantly as the CASE statement will interfere with index choice, however give it a go and hope for the best.

SELECT COALESCE(hsplit.requiredfield, hsplit2.requiredfield) AS requiredfield,
...
Left Join CMS_ECH.dbo.hsplit hsplit on hsplit.row_date = ANDREWSTABLE.SegStart_Date 
and hsplit.split=ANDREWSTABLE.dispsplit 
and hsplit.starttime = ANDREWSTABLE.Interval 
and hsplit.acd = ANDREWSTABLE.acd1
Left Join CMS_ECH.dbo.hsplit hsplit2 on hsplit2.row_date = ANDREWSTABLE.SegStart_Date 
and hsplit2.split=ANDREWSTABLE.dispsplit 
and hsplit2.starttime = ANDREWSTABLE.Interval 
and CASE WHEN hsplit2.acd = 1 THEN 4 WHEN hsplit2.acd = 4 THEN 1 WHEN ... END = ANDREWSTABLE.acd1