Alternative to "IN", which works as "AND" instead of "OR",

Question

From my understanding, IN works like this:

$arrayName = array(1, 2, 3); SELECT * FROM tableName WHERE productID IN ($arrayName) 

is equivalent to:

 SELECT * FROM tableName WHERE productID = 1 OR productID = 2 OR productID = 3 

I am wondering if there is an SQL function that works like IN but uses AND instead of OR to compare with an array. Something that would expand to this:

 SELECT * FROM tableName WHERE productID = 1 AND productID = 2 AND productID = 3 

Not that it was necessary, but for context, I just create a sort list for some search results that populate on a PHP page through jQuery. I can do what I need with PHP, I just create a query dynamically depending on what parameters the user has chosen, but I would prefer to use the intelligent SQL function, if possible.

*** EDIT: Thank you all for your help. I explained my problem very poorly, and you could still figure it out, which I appreciate. I found that someone even more eloquently asked this question and got an answer that I can use:

Is there something in MySQL like IN but that uses AND instead of OR?

I'm trying to figure out how to accept the answer and close it, but I have problems ...

Answer 1

You can't do it

 SELECT * FROM tableName WHERE productID = 1 AND productID = 2 AND productID = 3 

the condition always returns false , because a row can have only one value in its column, an alternative way to do this is to group the result, for example.

 SELECT colName FROM tableName WHERE productID IN (1,2,3) GROUP BY colName HAVING COUNT(DISTINCT colName) = 3 

having the condition HAVING COUNT(DISTINCT colName) = 3 , this means that the record instance must be equal to the total number of parameters provided in IN .

Answer 2

As written, your request will not contain strings. It is impossible for productID in a string to be both 1 and 2 at the same time.

You are probably looking for a group of strings containing these three products. Say you want to find orders that have all three products. You can use something like:

 select orderid from orderlines ol group by orderid havnig max(case when ol.productid = 1 then 1 else 0 end) > 0 and max(case when ol.productid = 2 then 1 else 0 end) > 0 and max(case when ol.productid = 3 then 1 else 0 end) > 0 

GROUP BY with HAVING will find orders where all three products are present.

Answer 3

 SELECT orderid FROM tableName WHERE productID IN (1, 2, 3) GROUP BY orderid HAVING COUNT(DISTINCT productID) = 3 --this number must match the number of unique IDs in the IN clause