SQL: Return "true" if list of records exists?
boolean, conditional-statements, exists, sql
Solution
Given your updated question, these are the simplest forms:
If `ProductID` is unique you want
SELECT COUNT(*) FROM Products WHERE ProductID IN (1, 10, 100)
and then check that result against `3`, the number of products you're querying (this last part can be done in SQL, but it may be easier to do it in C# unless you're doing even more in SQL).
If `ProductID` is not unique it is
SELECT COUNT(DISTINCT ProductID) FROM Products WHERE ProductID IN (1, 10, 100)
When the question was thought to require returning rows when all `ProductIds` are present and none otherwise:
SELECT ProductId FROM Products WHERE ProductID IN (1, 10, 100) AND ((SELECT COUNT(*) FROM Products WHERE ProductID IN (1, 10, 100))=3)
or
SELECT ProductId FROM Products WHERE ProductID IN (1, 10, 100) AND ((SELECT COUNT(DISTINCT ProductID) FROM Products WHERE ProductID IN (1, 10, 100))=3)
if you actually intend to do something with the results. Otherwise the simple `SELECT 1 WHERE (SELECT ...)=3` will do as other answers have stated or implied.
Problem
An alternative title might be: Check for existence of multiple rows? Using a combination of SQL and C# I want a method to return true if all products in a list exist in a table. If it can be done all in SQL that would be preferable. I have written a method that returns whether a single `productID` exists using the following SQL: ``` SELECT productID FROM Products WHERE ProductID = @productID ``` If this returns a row, then the c# method returns true, false otherwise. Now I'm wondering if I have a list of product IDs (not a huge list mind you, normally under 20). How can I write a query that will return a row if all the product id's exist and no row if one or more product id's does not exist? ``` (Maybe something involving "IN" like: SELECT * FROM Products WHERE ProductID IN ('1', '10', '100', 'ABC')) ``` EDIT: How the result is expressed is not important to me. Whether the query returns a `1` or `0`, an empty resultset or a non-empty one, true or false doesn't matter. I'd prefer the answer that is 1) easy to read and understand and 2) performant I was envisioning concatenating the list of product id's with the SQL. Obviously this opens the code up to SQL injection (the product id's are actually `varchar`. in this case the chance is slim but still want to avoid that possibility). So if there is a way around this that would be better. Using SQL Server 2005. Product ID's are `varchar`