Solved: Trying to select list not in the list of another l... - Page 2

WilsonWee · 07-10-2019 10:25 AM

Why does the IN() expression return N whereby, there are similar values in our Ref Table?

Please enlighten.

Steve

If there are any duplicates within list1, @tony’s expression may erroneously suggest a difference, since list subtraction has the side-effect of removing duplicates.

For instance, if list1 is {1, 1, 1} and list2 is {2}, {1, 1, 1} - {2} will give {1}, the COUNT() of which is less than COUNT({1, 1, 1}) but does not reflect the occurrence of an item in list2.

To avoid:

COUNT(list1 - LIST()) > COUNT(list1 - list2)

View solution in original post

Trying to select list not in the list of another list