Select by n conditions with AND from one column

Question

MySQL

Hello!
Please tell me the best option for searching the table in one column, using n parameters with the AND condition.
For example, there is a table: And you need to get productID where variantID=17 and optionID=6 and optionID=7. That is, as it were: but such a request will obviously not return anything, because optionID=6 AND optionID=7 cannot be on the same line. Thank you! UPD: clarified the question

productID___variantID___optionID
_711__________17_________7__
_712__________18_________7__
_700__________17_________7__
_701__________15_________7__
_713__________17_________7__
_708__________17_________7__
_699__________17_________6__
_699__________17_________7__





SELECT productID FROM tablname WHERE variantID=17 AND optionID=6 AND optionID=7;

Answer 1

SELECT t1.productID 
FROM tablename t1, tablename t2 
WHERE t1.optionID=6 AND t2.optionID=7 AND t1.productID=t2.productID;

Answer 2

Another option could be:

select p.productId from Products p where p.orderId = 6 and
exists(select productId from Products where productId = p.productId and orderId=7)

Answer 3

SELECT productID 
FROM 
    (SELECT productID, count(productID) AS cnt 
     FROM 
        (SELECT DISTINCT productID, optionID FROM tablname) as a1
    WHERE optionID IN (6,7)
    GROUP BY user_id) AS a2 
WHERE cnt=2

Scary? Me too. But it's simpler than the dohlik variant and scales easier.
In any case, the request is very heavy. What are you going to use this sample for? Maybe there is a way to do without such a heavy sample?

Answer 4

variantID you have 17 everywhere, and to select productID with optionID 6 and 7 use "OR:

SELECT productID FROM tablname WHERE variantID=6 OR variantID=7;

Answer 5

I'll try to compare two options:
Anei suggested a good, beautiful, easily scalable query.
But, in the question it sounded like this “Select by n conditions with AND”
let's say N = 10
then WHERE optionID IN (6,7,1,,,,23,,,)
HAVING cnt=10
Looks nice, but not very obvious as an optimizer can use indexes.
And a heaving group operation is very slow.
Since we are talking about an advanced search (which is not helped by caching), we can assume that this query will be performance sensitive. And under certain conditions, it can generally put a site.
Thus, I would consider the option that suggested tenbits (with inner join)
for scaling there, you just need to add one more inner join for each parameter.
For optimization, it is necessary to build a composite index optionID, productID (in that order),
then each join will make a selection exclusively from the index (there will be no access to the data of the table itself), and large-block by parameter.
in the selection, the products will be sorted by productID
i.e. it is very easy to join such selections.
This option is more predictable in terms of "query plan" with large N
But the real result will be very much envy of real data. We must experiment.
It is possible that the difference will not be noticed at all.