Dom Sinclair Dom Sinclair - 4 months ago 7
SQL Question

Sql: 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 arguments 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 ]  
)   
Comments