Search code examples
mysqlsqlwhere-in

Treat WHERE IN parameters as AND and not OR

I have this table structure/data :

enter image description here

I want to select the row_id(s) that have the combination of (97,6,2) AND (99,1,4) on (attribute_id,store_id,value) respectively.

In this example, we want to get the row_id 8664 as it respects this condition.

What I did is to use the where in statement like following :

SELECT DISTINCT row_id from catalog_product_entity_int 
where row_id in 
   (select row_id from catalog_product_entity_int 
    WHERE (attribute_id, store_id,value) 
    IN ( (99,1,4),(97,6,1) ))

This outputs the lines that have (99,1,4) OR (97,6,1).

I tried this query :

SELECT DISTINCT row_id from catalog_product_entity_int 
where row_id in 
    (select row_id from catalog_product_entity_int 
    WHERE (attribute_id, store_id,value) IN (99,1,4) 
    AND (attribute_id, store_id,value) IN(97,6,1) )

But I have #1241 - Operand should contain 3 column(s)

How do I manage to select the lines that validates the two conditions together ?

Solution

  • It's easy.

    Vectors (99, 1, 4) and (97, 6, 1) must be in parentheses: ((99, 1, 4)) and ((97, 6, 1)).

    Try:

    SELECT DISTINCT row_id 
FROM catalog_product_entity_int 
WHERE row_id IN (
    SELECT row_id 
    FROM catalog_product_entity_int 
    WHERE (attribute_id, store_id, value) IN ((99, 1, 4))
          OR (attribute_id, store_id, value) IN ((97, 6, 1))
)

    or even better:

    SELECT DISTINCT row_id 
FROM catalog_product_entity_int 
WHERE row_id IN (
    SELECT row_id 
    FROM catalog_product_entity_int 
    WHERE (attribute_id, store_id, value) IN ((99, 1, 4), (97, 6, 1))
)