SQL help - if then else(匹配一个条件，否则匹配另一个条件)

Question

我需要返回一个ACCOUNT_ID，如果它匹配3RD_PARTY_ID。如果没有匹配，我想在电子邮件地址上再匹配一次。

ACCOUNT_TABLE

[e-mail]

使用两个LEFT JOIN:

select at.*, coalesce(mt3.match_no, mte.match_no) as match_no
from ACCOUNT_TABLE at left join
MATCHING_TABLE mt3
ON at.3RD_PARTY_ID = mt3.MATCH_3RD_ID left join
MATCHING_TABLE mt3
ON at.EMAIL = mt.MATCH_EMAIL AND mt3.MATCH_3RD_ID IS NULL;

如果我理解正确的话:

select * 
from MATCHING_TABLE mt
left join ACCOUNT_TABLE at
on at.3RD_PARTY_ID = mt.MATCH_3RD_ID
or at.EMAIL = mt.MATCH_EMAIL

可以使用join…

SELECT M.MATCH_#, A.ACCOUNT_ID
FROM ACCOUNT_TABLE A
RIGHT JOIN MATCHING_TABLE M ON (M.MATCH_THIRD_ID = A.THIRD_PARTY_ID OR M.MATCH_EMAIL = A.EMAIL)
-- WHERE ISNULL(M.MATCH_#,'') <> '' -- Uncomment this line to retrieve only matching records