title: SQL : ISNULL in Join condition
body: <p>Is there any other way to write this. If the Credit cardID in Application table is NULL, then join CreditcardID from Terminatedcreditcard table. Thanks,</p>
  
  <p>This is the error: </p>
  
  <blockquote>
    <p>Msg 4104, Level 16, State 1, Line 18<br>
    The multi-part identifier "TC.CreditCardID" could not be bound.</p>
  </blockquote>
  
  <p>Code:</p>
  
  <pre><code>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 
  </code></pre>