[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.

[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>

[sapator]

OK so you can either OR it or UNION it.
Thanks.

[wqweto]

Btw, you usually (equi-)JOIN on FK columns i.e. FK constraint on it guaranties that all values there are strictly a subset of the values in some other (unique) PK column.

If you happen to be JOIN-ing on arbitrary columns then usually there are big issues with the DB design i.e. a big red flag is when you keep two things in a single column (LoyaltyMemberCode) and disambiguate on ISNUMERIC. This is the road to madness or really convoluted and poorly performing queries.

cheers,
</wqw>