dcsimg
Results 1 to 3 of 3

Thread: Which conditional statement do I use?

  1. #1
    Join Date
    Jun 2008
    Posts
    9

    Which conditional statement do I use?

    Is there a way to convert the following "multi-condition" SELECT statement into a "single-condition" SELECT statement? I need it to be one condition so it can be used inside a Stored Procedure. The problem I am having is the number of "AND" conditions can change.

    Code:
    SELECT pcSearchFields_Products.idProduct 
    FROM pcSearchFields_Products 
    WHERE 
    pcSearchFields_Products.idSearchData = 3 AND
    pcSearchFields_Products.idSearchData = 4

    In place of the example "AND" conditions I tried to use one statement of "IN (3,4)". However, "IN" returns true if any one of the values match whereas I need them all to match.

    Anyone have any ideas? I suspect this is simple for someone experienced with stored procedures.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,913
    First of all how can you have two values in the same column

    WHERE pcSearchFields_Products.idSearchData = 3 AND
    pcSearchFields_Products.idSearchData = 4

    If you do not know number of parameters that will be passed, then you need to build your SQL string dynamically and execute it.

  3. #3
    Join Date
    Jun 2008
    Posts
    9
    Thank you! I googled and found some information on dynamic stored procedures. That will do it.

    However, as you pointed out the query was flawed anyway. The following query is actually what I need to do. I would appreciate it if you had a suggestion. Essentially, I need to select all the products ids that are in each group. Here is what I have:

    Code:
    SELECT pcSearchFields_Products.idProduct FROM pcSearchFields_Products WHERE pcSearchFields_Products.idSearchData>0 
    AND pcSearchFields_Products.idProduct IN 
    	(
    	SELECT pcSearchFields_Products.idProduct 
    	FROM pcSearchFields_Products 
    	WHERE pcSearchFields_Products.idSearchData IN (3,4)
    	)
    AND pcSearchFields_Products.idProduct IN 
    	(
    	SELECT pcSearchFields_Products.idProduct 
    	FROM pcSearchFields_Products 
    	WHERE pcSearchFields_Products.idSearchData IN (4,5)
    	)
    ;
    This code does work. It returns the product id of "4" because it is in both groups. It would be nice if I could lump all the numbers into one statement and use something like "IN". Unfortunately, "IN" and "Exists" return true if there is one match. I need to make sure the product id in all the groups.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •