MINUS two Tables based on 2 columns

Question

Hi All,

I need to SELECT the rows of 'billing_temp' that doesn't exist in 'billing' based on two columns.

Currently I come-up with this query:

SELECT 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=billing_temp.ControlNum AND 
billing.CPTCode=billing_temp.CPTCode

But I'm getting the error:

Column 'ControlNum' in fieldlist is ambiguous.

Does anyone faced this occurrence.

If you need further details to recover this, inform me.. Help me. Thanx in advance..

NOTE :

I'm so sorry that I've found what is the problem in my own query.. Thanks for all who paid interest in my question and sent the answer..

Answer 1

Every occurence of a field name (in your query) where the field exists in more than one table must be fully qualified. In your case, either billing.ControlNum or billing_temp.ControlNum

Answer 2

Try:

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

The rule is: When you want to select all rows that appear in table A but do not appear at table B, you should select all the rows from "A LEFT JOIN B" that have NULLs in B.

Answer 3

If you want to SELECT the rows of 'billing_temp' that doesn't exist in 'billing', I'd try something along the lines of (untested!):

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
  )

Answer 4

Hi Intellects,

Thanks for all of your answers. I've found where I did mistake. Here's the absolute query:

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