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

User · Answer

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

User · Answer

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  gt  0       return true   else      return false    Or just make the condition shorter   return dt Rows Count  gt  0

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Here s how I usually do it   Just replace your query with this statement SELECT   FROM table WHERE 1     SELECT       CASE WHEN EXISTS                      SELECT   FROM table WHERE 1               THEN  TRUE        ELSE  FALSE     END

User · Answer

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

User · Answer

This may be too simple  but I always use   SELECT COUNT    gt 0 FROM  table  WHERE condition

User · Answer

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

User · Answer

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 lt Product gt    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

User · Answer

I know this is old but I think this will help anyone else who comes looking     SELECT CAST COUNT ProductID  AS bit  AS  EXISTS  FROM Products WHERE ProductID    ProductID    This will ALWAYS return TRUE if exists and FALSE if it doesn t  as opposed to no row

User · Answer

You can use a SELECT CASE statement like so   select case when EXISTS    select 1   from  lt table gt   where  lt condition gt     then TRUE else FALSE end   It returns TRUE when your query in the parents exists

User · Answer

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   gt  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

[c#] SQL: Return "true" if list of records exists?

Examples related to c#

Examples related to sql

Examples related to conditional

Examples related to boolean

Examples related to exists