3
\$\begingroup\$

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 option_id - 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:

enter image description here.

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

enter image description here

The Product variants table

enter image description here

Here stock and price will be real numbers.

The Options table

enter image description here

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

enter image description here

These are values for options.

The product_variant_values table

enter image description here

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 option_id and value_id.

Update 2: The combination for each option, option_id and 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 options_id's and value_id's that should be enough to find the variant_id.

\$\endgroup\$
  • \$\begingroup\$ From the query and the screenshots it doesn't seem like you need to do a join at all. Something like select * from product_variant_values where variant_id = '1' should do the job \$\endgroup\$ – Gentian Kasa Dec 3 '15 at 11:36
  • \$\begingroup\$ @GentianKasa I do not know variant_id. That's a trick. \$\endgroup\$ – FreeLightman Dec 3 '15 at 11:39
  • \$\begingroup\$ Then the query used for t2 should retrieve the required records. Your join clause does not alter (reduce or increase) the number of records in any way because it's being executed on the same table and on the same attribute. Try using just the query in brackets. \$\endgroup\$ – Gentian Kasa Dec 3 '15 at 11:43
  • \$\begingroup\$ @GentianKasa what you say - prntscr.com/99ti4d. what I did - prntscr.com/99tie8. You can see the difference. My way works. But I just wonder if I can use the classic join (join t2 on t1.field = t2.field2) instead of using sub query. \$\endgroup\$ – FreeLightman Dec 3 '15 at 11:51
  • \$\begingroup\$ What happens if you remove the group by clause (using 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) ) )? \$\endgroup\$ – Gentian Kasa Dec 3 '15 at 12:36
1
\$\begingroup\$

I'm not quite sure I understood your requirements, but as I understand it you want to find the variant id for a set of values with the values 1, 4, 7 for the three options.

In other words, you need to do a double join in order to access the three different values, this can be achieved using some thing like this:

SELECT t1.variant_id, t1.value_id t1v, t2.value_id t2v, t3.value_id t3v 
  FROM product_variant_values as t1
       JOIN product_variant_values AS t2 
         ON t1.variant_id = t2.variant_id AND t2.option_id = 2 AND t1.option_id = 1
       JOIN product_variant_values AS t3 
         ON t1.variant_id = t3.variant_id and t3.option_id = 3

When running this query as is, using a temporary table built from variant_id's up to 7 from http://prntscr.com/99sxp8, I got the following output:

variant_id t1v t2v t3v
1           1   4   7
2           1   4   8
3           1   4   9
4           1   5   7
5           1   5   8
6           1   5   9
7           1   6   7

Notice in this output how I've connected the t1 table to option_id=1, and t2 to option_id=2, and so on. Therefore when adding this line:

 WHERE t1.value_id = 1 AND t2.value_id = 4 AND t3.value_id = 7 

One can limit the query to a given value set:

variant_id t1v t2v t3v
1           1   4   7

In other words, variant_id = 1 is the only one having the combinations of values 1, 4, 7.

Update: Generalisation to multiple options

The method above could be extended to any number of options, but the cost of it expands for each option you add.

Another option for solving this kind of problem on this end, is to make n different selects, and then doing a intersection of matching variant_id's. If your final intersection has only one element, you've found your variant_id, and if no or more than one element you need to handle that accordingly.

Addendum: Ultimate solution

The solution above was what I came up with when focusing on the join part of your question, and your request to remove the second query. When thinking a little more on it, it's the first query you want to remove. Try this one on for size:

SELECT count(*), variant_id FROM product_variant_values
 WHERE (option_id = 1 AND value_id = 1)
    OR (option_id = 2 AND value_id = 4)
    OR (option_id = 3 AND value_id = 7)
GROUP BY variant_id
HAVING COUNT(variant_id) = 3

If you get more than one or none resulting row out of this query, you have a problem. This is also easily extensible, as you just need to change the matching option count on the HAVING COUNT section and add appropriate extra groups on the OR parts.

\$\endgroup\$
  • \$\begingroup\$ That's what I want. Really simple. Thank you. \$\endgroup\$ – FreeLightman Dec 4 '15 at 13:57
1
\$\begingroup\$

Let's just look at your subselect:

select `t1`.* from `product_variant_values` as t1
…
group by t1.variant_id having count(t1.id) = 3

That is non-standard SQL, and doesn't actually make sense. Whenever you do a GROUP BY, all of the columns being selected must either be named in the GROUP BY clase (variant_id in this case) or be an aggregate function.

MySQL, being a weird database, allows this illegal query anyway when the global ONLY_FULL_GROUP_BY option is disabled. (If is disabled by default in MySQL < 5.7.5, and on by default in MySQL ≥ 5.7.5.)

\$\endgroup\$
  • \$\begingroup\$ so if I write select `t1`.variant_id it will be legal ? \$\endgroup\$ – FreeLightman Dec 3 '15 at 23:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.