checking if values of an array are in select statement

Question

I have the following query which checks my tables for rows where the AccountNumber is not found in an array:

--Select the total number of domestic keypad customers who left in march
select New_FirstName,New_LastName,New_AccountNumber,New_KeyPadPremiseNo ,New_DateCustomerLeft, c.Line1,c.Line2,c.Line3,c.City,c.PostOfficeBox from AccountExtensionBase as a
inner join CustomerAddressBase as c on a.AccountId = c.ParentId
where New_DateCustomerLeft between '1 feb 2015' and '1 mar 2015'
and AddressTypeCode = 1
and New_AccountType = 5
and New_AccountNumber not in (123,456,789,101112,131415)

How can I check if my array contains a number that is not found in the database like:

select from ((123,456,789,101112,131415)
where not in (select from table)

Its not clear to me what you want,whats wrong with your first query? — Mihai, Apr 17 '15 at 22:23
The values that are in the array are account numbers from a spreadsheet source, I wanted to check for account numbers that were in the table but not in the array (this works fine) but I now want to check account numbers that are in the array and not in the table if that makes it any clearer as to what I am trying to accomplish — Jay, Apr 17 '15 at 22:29

score 0 · Answer 1 · answered Apr 17 '15 at 23:01

You can use SQL except, if you drop your first list into a temp table (excel formulas are handy for quickly creating a bunch of insert statements, if necessary):

Select number from TempTable
except
Select number  from table

or

Select * from TempTable
where number not in (select number from table)

checking if values of an array are in select statement

1 Answers1