SQL：连接条件下的ISNULL [英] SQL : ISNULL in Join condition

问题描述

 
$


有没有其他方法可以写这个。如果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



<来自[HydraDB]。[dbo]。[StagingLLSPEXSettlements] PT  





左连接（选择一个.Patientid

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，a.Applicationid

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;  ，ISnull（a.CreditCardId，TC.CreditCardID）为CreditCardId

  &NBSP; &NBSP; &NBSP; &NBSP;  来自HYDRADB..Application a

左连接HYDRADB..TerminatedCreditCard TC  &NBSP; &NBSP; 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

这篇关于SQL：连接条件下的ISNULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！