0

I'm trying to generate a list of data which is on completed status but has not been billed. I am able to produce a list of all completed orders however i am unable to remove the ones which have been billed. In a separate table is a list of reports, once a report has been billed a number is populated in the Report_Billing_Num column. How do i get the below code to show non-billed orders?

SELECT
      [Call_Num],
      [Call_Cust_Num],
      [Call_Cust Name],
      [Call_Status]
FROM tableCall

JOIN tableREPORTS
ON tableCALL.Call_Num = tableREPORTS.Report_Call_Num

WHERE tableCALL.Call_Status = 'COMP' AND tableREPORT.Report_Billing_Num IS NULL

ORDER BY Call_Status

This code kind of works, however in the tableREPORTS table several rows can be in the table for the same Call_Num which seems to be showing duplicated when i run this script. How would i have it so duplicated aren't shown? Would it be better to use a NOT IN or NOT EXISTS query?

  • 1
    Why are they called tableCall and tableREPORTS? You haven't named your columns columnCall_Num etc? Why not? Sorry nameJamie :P – Philᵀᴹ Jun 22 '16 at 21:42
  • Well the real columns and tables are called something slightly different. The column names are specified after the dot (i.e., tableCALL.COUMN_NAME) – Jamie Jun 22 '16 at 21:52
1

Would it be better to use a NOT IN or NOT EXISTS query?

Yes, you would most likely benefit from an IN() (or EXISTS()) instead of the join and IS NULL because you're effectively only using the tableREPORTS data as a filter.

This should work for what you need to do:

SELECT
      [Call_Num],
      [Call_Cust_Num],
      [Call_Cust Name],
      [Call_Status]
FROM tableCall
JOIN tableREPORTS
ON tableCALL.Call_Num = tableREPORTS.Report_Call_Num
WHERE tableCALL.Call_Status = 'COMP' 
  AND tableCALL.Call_Num in(select tableREPORTS.Report_Call_Num from tableREPORTS where tableREPORT.Report_Billing_Num IS NULL)
ORDER BY Call_Status

How would i have it so duplicated aren't shown?

If there are duplicate values in tableREPORTS.Reportz_Call_Num, then eliminating the join should eliminate duplicate records in the results. However, you may still get duplicate records in the results if the tableCall.Call_Num column isn't unique or a primary key.

  • Excellent that has done exactly what I wanted, I took out the JOIN part and it seems to show what I want without duplicates - Thank you – Jamie Jun 24 '16 at 9:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.