SQL: Return "true" if list of records exists?

Question

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

Answer 1

Your c# will have to do just a bit of work (counting the number of IDs passed in), but try this:

select (select count(*) from players where productid in (1, 10, 100, 1000)) = 4

Edit:

4 can definitely be parameterized, as can the list of integers.

If you're not generating the SQL from string input by the user, you don't need to worry about attacks. If you are, you just have to make sure you only get integers. For example, if you were taking in the string "1, 2, 3, 4", you'd do something like

String.Join(",", input.Split(",").Select(s => Int32.Parse(s).ToString()))

That will throw if you get the wrong thing. Then just set that as a parameter.

Also, be sure be sure to special case if items.Count == 0, since your DB will choke if you send it where ParameterID in ().

Answer 2

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.

Answer 3

Assuming you're using SQL Server, the boolean type doesn't exist, but the bit type does, which can hold only 0 or 1 where 0 represents False, and 1 represents True.

I would go this way:

select 1
    from Products
    where ProductId IN (1, 10, 100)

Here, a null or no row will be returned (if no row exists).

Or even:

select case when EXISTS (
    select 1
        from Products
        where ProductId IN (1, 10, 100)
    ) then 1 else 0 end as [ProductExists]

Here, either of the scalar values 1 or 0 will always be returned (if no row exists).

Answer 4

// not familiar with C#, but C#'s equivalent of PHP's:
$count = count($productIds); // where $productIds is the array you also use in IN (...)

SELECT IF ((SELECT COUNT(*) FROM Products WHERE ProductID IN (1, 10, 100)) = $count, 1, 0)

Answer 5

If the IN clause is a parameter (either to SP or hot-built SQL), then this can always be done:

SELECT (SELECT COUNT(1)
          FROM product_a
         WHERE product_id IN (1, 8, 100)
       ) = (number of commas in product_id as constant)

If the IN clause is a table, then this can always be done:

SELECT (SELECT COUNT(*)
          FROM product_a
         WHERE product_id IN (SELECT Products
                                FROM #WorkTable)
       ) = (SELECT COUNT(*)
              FROM #WorkTable)

If the IN clause is complex then either spool it into a table or write it twice.

Answer 6

If you have the IDs stored in a temp table (which can be done by some C# function or simple SQL) then the problem becomes easy and doable in SQL.

select "all exist"
where (select case  when count(distinct t.id) = (select count(distinct id) from #products) then "true" else "false" end
    from ProductTable t, #products p
    where t.id = p.id) = "true"

This will return "all exists" when all the products in #products exist in the target table (ProductTable) and will not return a row if the above is not true.

If you are not willing to write to a temp table, then you need to feed in some parameter for the number of products you are attempting to find, and replace the temp table with an 'in'; clause so the subquery looks like this:

SELECT "All Exist"
WHERE(
        SELECT case when count(distinct t.id) = @ProductCount then "true" else "false" 
        FROM ProductTable t 
        WHERE t.id in (1,100,10,20) -- example IDs
) = "true"

Answer 7

@Mark Hurd, thanks for pointing out the error.

this will work (if you are using Postgresql, Sql Server 2008):

create table products
(
product_id int not null
);



insert into products values(1),(2),(10),(100);

SELECT 
    CASE 
        WHEN EXISTS(
             SELECT 1 
             FROM (values(1),(10),(100)) as x(id) 
             WHERE x.id NOT IN (select product_id from products))
        THEN 0 --'NOT ALL'

        ELSE 1 -- 'ALL'
    END

If you are using MySQL, make a temporary memory table(then populate 1,10,100 there):

create table product_memory(product_id int) engine=MEMORY;

insert into product_memory values(1),(10),(100);

SELECT 
    CASE 
        WHEN EXISTS(
             SELECT 1 
             FROM product_memory
             WHERE product_memory.id NOT IN (select product_id from products))
        THEN 0 -- 'NOT ALL'

        ELSE 1 -- 'ALL'
    END

On your C# code:

bool isAllExist = (int)(new SqlCommand(queryHere).ExecuteScalar()) == 1;

[EDIT]

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?

Regarding, returning a row(singular) if all rows exists, and no row to be returned if one or more product id does not exists:

MySql:

SELECT 1
WHERE 
    NOT EXISTS(
        SELECT 1
             FROM product_memory
             WHERE product_memory.id NOT IN (select product_id from products) )

Posgresql, Sql Server 2008:

SELECT 1
WHERE 
    NOT EXISTS(            
        SELECT 1 
        FROM (values(1),(10),(100)) as x(id) 
        WHERE x.id NOT IN (select product_id from products) )

Then on your C# code:

var da = new SqlDataAdapter(queryhere, connectionhere);
var dt = new DataTable();
da.Fill(dt);

if (dt.Rows.Count > 0) 
    return true; 
else 
    return false;

Or just make the condition shorter:

return dt.Rows.Count > 0;

Answer 8

If you are using SQL Server 2008, I would create a stored procedure which takes a table-valued parameter. The query should then be of a particularly simple form:

CREATE PROCEDURE usp_CheckAll 
    (@param dbo.ProductTableType READONLY)
AS
BEGIN
    SELECT CAST(1 AS bit) AS Result
    WHERE (SELECT COUNT(DISTINCT ProductID) FROM @param)
        = (SELECT COUNT(DISTINCT p.ProductID) FROM @param AS p
           INNER JOIN Products 
               ON p.ProductID = Products.ProductID)
END

I changed this to return a row, as you seem to require. There are other ways to do this with a WHERE NOT EXISTS (LEFT JOIN in here WHERE rhs IS NULL):

CREATE PROCEDURE usp_CheckAll 
    (@param dbo.ProductTableType READONLY)
AS
BEGIN
    SELECT CAST(1 AS bit) AS Result
    WHERE NOT EXISTS (
        SELECT * FROM @param AS p
        LEFT JOIN Products 
            ON p.ProductID = Products.ProductID
        WHERE Products.ProductID IS NULL
    )
END

Answer 9

Where is this list of products that you're trying to determine the existence of? If that list exists within another table you could do this

declare @are_equal bit
declare @products int

SELECT @products = 
     count(pl.id)
FROM ProductList pl
JOIN Products p
ON pl.productId = p.productId

select @are_equal = @products == select count(id) from ProductList

Edit:

Then do ALL the work in C#. Cache the actual list of products in your application somewhere, and do a LINQ query.

var compareProducts = new List<Product>(){p1,p2,p3,p4,p5};
var found = From p in GetAllProducts()
            Join cp in compareProducts on cp.Id equals p.Id
            select p;

return compareProducts.Count == found.Count;

This prevents constructing SQL queries by hand, and keeps all your application logic in the application.

Answer 10

DECLARE @values TABLE (ProductId int)
INSERT @values (1)
INSERT @values (10)
INSERT @values (100)

SELECT CASE WHEN (SELECT COUNT(*) FROM @values v) = 
                 (SELECT COUNT(*) FROM Products p WHERE p.ProductId IN
                       (SELECT v.ProductId FROM @values v))
            THEN CAST(1 AS bit)
            ELSE CAST(0 AS bit)
       END [AreAllFound]