[RESOLVED] Case statement in inner join

[sapator]

Hi.
MS SQL , is there a way to case so you can have a different join per contrition?

So something like:

select * from TBank  T
inner join tblDWLoyaltyMember L on (Case WHEN  ISNUMERIC(T.LoyaltyMemberCode) = 1 THEN (L.LoyaltyMemberCode = T.LoyaltyMemberCode) ELSE (L.MembershipID = T.LoyaltyMemberCode) END)

ELSE I only seem to think of this:

left join tblDWLoyaltyMember L  on L.MembershipID = T.LoyaltyMemberCode 
left join tblDWLoyaltyMember L1 on L1.LoyaltyMemberCode = T.LoyaltyMemberCode

but I'm not sure if this is correct as I left join something that I wan to only be on inner join

Thanks.