Alternative to "IN" that works like "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 the equivalent of:

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

I'm wondering if there's a SQL function that works like IN but uses AND in place of OR to compare to an array. Something that would expand to this:

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

Not that it's necessary, but for context I'm simply creating a sort list for some search results that are being populated on a PHP page via jQuery. I can do what I need with PHP, I'll simply create the query dynamically depending on what options the user has selected, but I'd rather use an intelligent SQL function if possible.

***EDIT: Thanks everyone for the help. I explained my problem very poorly and you were still able to sort it out, which I appreciate. I found that someone else had asked this question more eloquently and received an answer that I can use:

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

I'm trying to figure out how to accept an answer and close this but I'm having a bit of trouble...

Answer 1

You cannot possibly do this,

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

the condition will always returns false because a row can have only one value on its column, the alternative way to do this is by grouping the result, ex.

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

by having a condition HAVING COUNT(DISTINCT colName) = 3, this means that the instance of a record must be equal to the total count of parameters supplied on IN clause.

Answer 2

As written, your query will produce no rows. It is not possible for productID in a row to be equal to both 1 and 2 at the same time.

You are probably looking for a group of rows that contain 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

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