[RESOLVED] Case statement in inner join

[wqweto]

Yes, you can. But you should probably reconsider as it will incur abysmal performance

SELECT  *
FROM    TBank T
JOIN    tblDWLoyaltyMember L
ON      (ISNUMERIC(T.LoyaltyMemberCode) = 1 AND T.LoyaltyMemberCode = L.LoyaltyMemberCode)
        OR (ISNUMERIC(T.LoyaltyMemberCode) = 0 AND T.LoyaltyMemberCode = L.MembershipID)

Try to replace the abomination above with UNION like this

SELECT  *
FROM    TBank T
JOIN    tblDWLoyaltyMember L
ON      T.LoyaltyMemberCode = L.LoyaltyMemberCode
WHERE   ISNUMERIC(T.LoyaltyMemberCode) = 1

UNION ALL

SELECT  *
FROM    TBank T
JOIN    tblDWLoyaltyMember L
ON      T.LoyaltyMemberCode = L.MembershipID
WHERE   ISNUMERIC(T.LoyaltyMemberCode) = 0

cheers,
</wqw>