conditional inner or outer join sql server

Question

I would like to decide Inner or outer join ( to the same table) depending on the value. How do I do this? Thanks

I have all my select values here Then bunch of joins And on the last join I would like to do

  CASE 
WHEN RTRIM(LTRIM(@AccountType)) = 'OFX' THEN
INNER JOIN Subscriber.Access.SubscriberOFXAccount  ASOA
                ON SSA.Id = ASOA.SubscriberOFXAccountId AND ASOA.Active = 1
ELSE
LEFT JOIN Subscriber.Access.SubscriberOFXAccount  ASOA2
                ON SSA.Id = ASOA2.SubscriberOFXAccountId 

Answer 1

You just use an if/else condition to make up the SQL Statement completly:

IF RTRIM(LTRIM(@AccountType)) = 'OFX' THEN
 BEGIN
   SELECT Blah FROM Table WHERE Blah2=SomeThings INNER JOIN MyOtherFooBar ON ...
 END
ELSE
 BEGIN
   SELECT Blah FROM Table WHERE Blah2=SomeThings LEFT JOIN MyOtherFooBar ON ...
 END

Answer 2

How about:

Select ..
From ...
    Left Join Subscriber.Access.SubscriberOFXAccount ASOA 
        ON SSA.Id = ASOA.SubscriberOFXAccountId 
Where RTRIM(LTRIM(@AccountType)) <> 'OFX' Or ASOA.IsActive = 1

Note that by check for IsActive = 1 on a left join, it effectively means a row must exist and thus when @AccountType is not OFX, it will effectively use an inner join.