基于2列减去2个表

如果字段存在于多个表中,则每次出现字段名(在查询中)都必须完全限定。对你来说也是 billing.ControlNum billing_temp.ControlNum

如果您想选择“billing\u temp”中不存在的“billing\u temp”行,我可以尝试以下方法(

SELECT
  ControlNum, CarrierName, PhoneNum, PatientName, SubscriberID, SubscriberName, 
  ChartNum, DoB, SubscriberEmp, VisitID, ServiceDate, ProviderName, CPTCode, BillingDate, 
  AgingDate, BalanceAmt, Age, AgeCategory 
FROM
  billing_temp
WHERE 
  NOT EXISTS (
    SELECT
      * 
    FROM 
      billing 
    WHERE 
      billing.ControlNum=billing_temp.ControlNum AND 
      billing.CPTCode=billing_temp.CPTCode
  )

以下是绝对查询:

SELECT bt.ControlNum, bt.CarrierName, bt.PhoneNum, bt.PatientName, bt.SubscriberID, 
bt.SubscriberName, bt.ChartNum, bt.DoB, bt.SubscriberEmp, bt.VisitID, bt.ServiceDate, 
bt.ProviderName, bt.CPTCode, bt.BillingDate, bt.AgingDate, bt.BalanceAmt, bt.Age, 
bt.AgeCategory FROM billing_temp bt LEFT JOIN billing ON 
bt.ControlNum=billing.ControlNum AND bt.CPTCode=billing.CPTCode

尝试:

SELECT billing_temp.ControlNum, CarrierName, PhoneNum, PatientName, SubscriberID, SubscriberName, 
ChartNum, DoB, SubscriberEmp, VisitID, ServiceDate, ProviderName, CPTCode, BillingDate, 
AgingDate, BalanceAmt, Age, AgeCategory FROM billing_temp LEFT JOIN billing 
USING (ControlNum, CPTCode) WHERE billing.ControlNum IS NULL

规则是: 如果要选择表A中出现但未出现在表B中的所有行,则应选择“A LEFT JOIN B”中在B中为空的所有行。