Dom Sinclair Dom Sinclair - 1 year ago 66
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?


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 ]  
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download