sql - SQL：连接条件中的 ISNULL

Question

有没有其他方法可以写这个。如果 Application 表中的 CreditcardID 为 NULL，则从 Terminatedcreditcard 表中加入 CreditcardID。谢谢，

这是错误：

消息 4104，级别 16，状态 1，第 18 行
无法绑定多部分标识符“TC.CreditCardID”。

代码：

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 
    [dbo].[StagingSettlements] PT  
LEFT JOIN 
    dbo.Application a ON PT.[CustomId] = ISNULL(a.CreditCardId, TC.CreditCardID)
LEFT JOIN 
    dbo.TerminatedCreditCard TC ON TC.ApplicationId = a.ApplicationId 

标签： sqlsql-server

Answer 1

您可以更改加入优先级以获取此信息：

FROM [dbo].[StagingSettlements] AS PT  
LEFT JOIN ( dbo.Application AS a
    LEFT JOIN dbo.TerminatedCreditCard AS TC on TC.ApplicationId = a.ApplicationId )
        ON PT.[CustomId] = ISNULL( a.CreditCardId, TC.CreditCardID )

上面添加括号会导致Application首先TerminatedCreditCard评估 join ，然后将结果连接到StagingSettlements。