My current query:
select `t1`.* from `product_variant_values` as t1 inner join ( select `t1`.* from `product_variant_values` as t1 where ( (t1.`option_id` = 1 and t1.`value_id` = 1) or (t1.`option_id` = 2 and t1.`value_id` = 4) or (t1.`option_id` = 3 and t1.`value_id` = 7) ) group by t1.variant_id having count(t1.id) = 3 ) t2 on t2.variant_id = t1.variant_id
I have this query, and I want to know how I can get rid of the second select and just use a simple join? Here is a link to a picture of my main table: http://prntscr.com/99sxp8 . I set
where condition for
value_id pair and want to select those group of rows which have the same
variant_id and number of rows is 3.
Update: I do not know the amount of needed rows. I just have an array of needed arguments:
The keys are
option_id and the value is
value_id. These will differ, and I want to select rows which match the arguments, and the number of rows must be equal to the count of options (or
count($arguments)) and all rows must have the same
variant_id, which is what I'm looking for.
Update 2: Here is the full picture of my tables.
The products table
The Product variants table
Here stock and price will be real numbers.
The Options table
Also I have option_group table, but it consists of just an id and a name. It is as option set. One product will have only one option group.
The table variantable_option_variants
These are values for options.
The product_variant_values table
This table contains all possible combination for option values and parent variant. One example: "red color - s size - long height", matches option_id/value_id set of
(1:1, 2:4, 3:7).
I want to find
variant_id (from product_variant_values) by a set of combinations of
Update 2: The combination for each option,
value_id really exists. They are created when admin saves the option group.
User have dropdown lists for each of the different options. And when he submits the form, I get the combinations of
value_id's that should be enough to find the