1

Table1 has only one column (customer_id) and contains a list of customer ids I want to exclude from my analysis.
OverallSalesTable contains customer_id too (all the ones I want to exclude + others I want to include) and other attributes (I kept only sale_amount here)

I'm doing this :

Select a1.customer_id, a1.sales_amount
from OverallSalesTable a1
left join Table1 a2 on a1.customer_id = a2.customer_id
where a1.customer_id not in a2.customer_id

But the last line causes an error.
If I do where a1.customer_id <> a2.customer_id instead, no error but it returns a blank table (no values). I am 100% sure that OverallSalesTable contains some customer_id that are not in Table1.

Is there a nice way to do this?

Thom A
  • 88,727
  • 11
  • 45
  • 75
Soraqil
  • 39
  • 4
  • Does this answer your question? [How to select all records from one table that do not exist in another table?](https://stackoverflow.com/questions/2686254/how-to-select-all-records-from-one-table-that-do-not-exist-in-another-table) – philipxy Jun 16 '22 at 04:08

1 Answers1

2

Just use NOT EXISTS. NOT EXISTS will be true or false. While using a WHERE a = b, you will remove all items, which have the same value.

Try this:

SELECT a1.customer_id, a1.sales_amount
FROM OverallSalesTable a1 left join Table1 a2 
ON a1.customer_id = a2.customer_id
WHERE NOT EXISTS 
(SELECT a2.customer_id FROM Table1 WHERE a1.customer_id = a2.customer_id)
robni
  • 904
  • 2
  • 6
  • 20
  • 1
    Thank you @robni, will have to investigate a bit further, it seems to return results but when I compare with and without that condition, I have a difference in sales of X However when I count the sum of the banned customers, I get Y (Y = 1.3*X roughly). Not all of them seem to be blocked – Soraqil Jun 15 '22 at 13:43