In general, such a request

SELECT `product_id` FROM `oc_product_option_value` WHERE `option_value_id`='49' AND `option_value_id`='50' ORDER BY `product_id` ASC

Trying to select id entries whose value is option_value_id = '50 'and option_value_id = '49' It is necessary to output id that have exactly 2 such matches, if only 1 entry matches, then id should not be included in the selection.

The records in the database have the following format. enter image description here

  • How can your optional_value_id have two values ​​at once on a single line? And if you need to select two strings, in which optional_value_id is equal to either one value or another, then AND must be replaced by OR . - Sergey Gornostaev
  • the point is to choose the id which has both the value 49 and the value 50, but the campaign cannot be implemented through sql so the sample cannot be implemented, you probably need to make a condition using php and check for a match. - igorauscas
  • one
    @ igorauscas, you can do everything in sql) - Yaroslav

1 answer 1

Somewhere like this:

  SELECT `product_id`, COUNT (DISTINCT (` option_value_id`)) AS `options` 
 FROM `oc_product_option_value` WHERE` option_value_id` = '49 'OR `option_value_id` = '50' 
 GROUP BY `product_id` HAVING` options` = '2'
  • And how do you do the opposite? That is, select products for which there is only one match, for example, in the field option_value_id = '49 'If you make such a SELECT query product_id , COUNT (DISTINCT ( option_value_id )) AS variants FROM oc_product_option_value WHERE option_value_id = '49 HAVING variants = '1' then it will display all the entries that have a match in one of the columns - igorauscas
  • A little did not understand the problem. What exactly needs to be received and how should it differ from the option with HAVING variants='1 options HAVING variants='1 ? - Ivan Olshansky
  • Variant of HAVING variants = '1' selects all positions that have a match, or option_value_id = '49 'or option_value_id = '50' And I need the request to check if the position does not have matches for both options, then we display positions only that have a match for option_value_id = '49 ' - igorauscas
  • And how will this differ from the query only in those positions that have a match on option_value_id = '49 '? After all, in fact, in this case, we will get those positions that have both options, option_value_id = '49 'and option_value_id = '50', and those positions that have only option_value_id = '49 '. - Ivan Olshansky