SQL：连接条件下的ISNULL
$
有没有其他方法可以写这个。如果Application表中的Credit cardID为NULL，则从Terminatedcreditcard表加入CreditcardID。谢谢，
Hi,
Is there any other way to write this. If the Credit cardID in Application table is NULL, then join CreditcardID from Terminatedcreditcard table. Thanks,
选择Distinct prg.Title as Program
，a.Patientid
，a.Applicationid
，PT.MCC
，PT.MerchantName
，PT.MerchantCity
，PT.MerchantState
，PT.MerchantZip
，PT.SettlementTransactionID
，将（日期，PT.SettlementDate）转换为SettlementDate
，ABS（PT.Amount）为TransactionAmount来自[HydraDB]的
。[dbo]。[StagingLLSPEXSett lements] PT
$
左加入HYDRADB..Application a PT上的
。[CustomId] = ISnull（a.CreditCardId，TC.CreditCardID）
左加入HYDRADB..TerminatedCreditCard TC     on TC.ApplicationId = a.ApplicationId
select Distinct prg.Title as Program
, a.Patientid
, a.Applicationid
, PT.MCC
, PT.MerchantName
, PT.MerchantCity
, PT.MerchantState
, PT.MerchantZip
, PT.SettlementTransactionID
, convert(Date,PT.SettlementDate) as SettlementDate
, ABS(PT.Amount) as TransactionAmount
from [HydraDB].[dbo].[StagingLLSPEXSettlements] PT
Left Join HYDRADB..Application a
on PT.[CustomId] = ISnull(a.CreditCardId, TC.CreditCardID)
Left join HYDRADB..TerminatedCreditCard TC on TC.ApplicationId = a.ApplicationId
选择Distinct prg.Title as程序
，x.Patientid
，x.Applicationid
，PT.MCC
，PT.MerchantName
，PT.MerchantCity
，PT。 MerchantState
，PT.MerchantZip
，PT.SettlementTransactionID
，转换（日期，PT.SettlementDate）作为SettlementDate
，ABS（PT.Amount）为TransactionAmount
左连接（选择一个.Patientid
              ，a.Applicationid
              ，ISnull（a.CreditCardId，TC.CreditCardID）为CreditCardId
        来自HYDRADB..Application a
左连接HYDRADB..TerminatedCreditCard TC     on TC.ApplicationId = a.ApplicationId
  ; ）x
PT。[CustomId] = x.CreditCardId
select Distinct prg.Title as Program
, x.Patientid
, x.Applicationid
, PT.MCC
, PT.MerchantName
, PT.MerchantCity
, PT.MerchantState
, PT.MerchantZip
, PT.SettlementTransactionID
, convert(Date,PT.SettlementDate) as SettlementDate
, ABS(PT.Amount) as TransactionAmount
from [HydraDB].[dbo].[StagingLLSPEXSettlements] PT
Left Join (select a.Patientid
,a.Applicationid
,ISnull(a.CreditCardId, TC.CreditCardID) as CreditCardId
From HYDRADB..Application a
Left join HYDRADB..TerminatedCreditCard TC on TC.ApplicationId = a.ApplicationId
)x
on PT.[CustomId] = x.CreditCardId