Returning data based on if another column in another table is NULL

Question

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?

Answer 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.