Dom Sinclair Dom Sinclair - 1 year ago 57
SQL Question

SQL Server : is it possible to restrict a where clause to specific values in a pre-declared list

I need to run a simple query against a purchase invoices table to sum the value of a subset of suppliers invoices. Normally this would be a simple case of the following pseudo query;

Select Sum(invoiceTotal)
From PurchaseInvoices
Where ContactId = x

The problem that I have is that the particular suppliers that I'm after are randomly distributed throughout the contacts table so I can't simply amend the where clause to say
Between x and y
as it would draw in records I don't want.

If for argument's sake I need to sum the values of contacts a,b,c,g,j,k,s and u is there a way to define a list of contacts at the outset of a SQL query and then simply restrict the where clause to
's that fall within that list?


Answer Source

You can use IN clause to provide a list or sub-query that returns the list.

Select Sum(invoiceTotal) From PurchaseInvoices
Where ContactId in (x, y, z) 


test_expression [ NOT ] IN   
( subquery | expression [ ,...n ]