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.