Dom Sinclair Dom Sinclair - 4 months ago 6
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
ContactId
's that fall within that list?

Thanks

Answer

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) 

Syntax

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