Products multiple variants

Question

My query looks like:

select p.id, p.name, vo.value, v.name
from products p
join products_variant_options pvo on pvo.product_id = p.id
join variant_options vo on vo.id = pvo.variant_option_id
join variants v on v.id = vo.variant_id

and return rows like these:

+----+-------------------------------+---------+-----------+
| id | name                          | value   | name      |
+----+-------------------------------+---------+-----------+
|  2 | T-shirt red cotton small      | Red     | Colors    |
|  2 | T-shirt red cotton small      | Cotton  | Materials |
|  2 | T-shirt red cotton small      | S       | Sizes     |
|  3 | T-shirt red cotton medium     | Red     | Colors    |
|  3 | T-shirt red cotton medium     | Cotton  | Materials |
|  3 | T-shirt red cotton medium     | M       | Sizes     |
|  4 | T-shirt red cotton large      | Red     | Colors    |
|  4 | T-shirt red cotton large      | Cotton  | Materials |
|  4 | T-shirt red cotton large      | L       | Sizes     |
|  5 | T-shirt red leather small     | Red     | Colors    |
|  5 | T-shirt red leather small     | Leather | Materials |
|  5 | T-shirt red leather small     | S       | Sizes     |
|  6 | T-shirt red leather medium    | Red     | Colors    |
|  6 | T-shirt red leather medium    | Leather | Materials |
|  6 | T-shirt red leather medium    | M       | Sizes     |
|  7 | T-shirt red leather large     | Red     | Colors    |
|  7 | T-shirt red leather large     | Leather | Materials |
|  7 | T-shirt red leather large     | L       | Sizes     |
|  8 | T-shirt blue cotton small     | Blue    | Colors    |
|  8 | T-shirt blue cotton small     | Cotton  | Materials |
|  8 | T-shirt blue cotton small     | S       | Sizes     |
|  9 | T-shirt blue cotton medium    | Blue    | Colors    |
|  9 | T-shirt blue cotton medium    | Cotton  | Materials |
|  9 | T-shirt blue cotton medium    | M       | Sizes     |
| 10 | T-shirt blue cotton large     | Blue    | Colors    |
| 10 | T-shirt blue cotton large     | Cotton  | Materials |
| 10 | T-shirt blue cotton large     | L       | Sizes     |
| 11 | T-shirt blue leather small    | Blue    | Colors    |
| 11 | T-shirt blue leather small    | Leather | Materials |
| 11 | T-shirt blue leather small    | S       | Sizes     |
| 12 | T-shirt blue leather medium   | Blue    | Colors    |
| 12 | T-shirt blue leather medium   | Leather | Materials |
| 12 | T-shirt blue leather medium   | M       | Sizes     |
| 13 | T-shirt blue leather large    | Blue    | Colors    |
| 13 | T-shirt blue leather large    | Leather | Materials |
| 13 | T-shirt blue leather large    | L       | Sizes     |
| 14 | T-shirt yellow cotton small   | Yellow  | Colors    |
| 14 | T-shirt yellow cotton small   | Cotton  | Materials |
| 14 | T-shirt yellow cotton small   | S       | Sizes     |
| 15 | T-shirt yellow cotton medium  | Yellow  | Colors    |
| 15 | T-shirt yellow cotton medium  | Cotton  | Materials |
| 15 | T-shirt yellow cotton medium  | M       | Sizes     |
| 16 | T-shirt yellow cotton large   | Yellow  | Colors    |
| 16 | T-shirt yellow cotton large   | Cotton  | Materials |
| 16 | T-shirt yellow cotton large   | L       | Sizes     |
| 17 | T-shirt yellow leather small  | Yellow  | Colors    |
| 17 | T-shirt yellow leather small  | Leather | Materials |
| 17 | T-shirt yellow leather small  | S       | Sizes     |
| 18 | T-shirt yellow leather medium | Yellow  | Colors    |
| 18 | T-shirt yellow leather medium | Leather | Materials |
| 18 | T-shirt yellow leather medium | M       | Sizes     |
| 19 | T-shirt yellow leather large  | Yellow  | Colors    |
| 19 | T-shirt yellow leather large  | Leather | Materials |
| 19 | T-shirt yellow leather large  | L       | Sizes     |
+----+-------------------------------+---------+-----------+

As you see, there are duplicates... this is first problem.

The second problem is how to return results just based on color and material (as just one) without size variant? Excepted results:

+----+-------------------------------+
| id | name                          |
+----+-------------------------------+
|  2 | T-shirt red cotton small      |
|  6 | T-shirt red leather medium    |
|  7 | T-shirt blue cotton large     |
| 13 | T-shirt blue leather large    |
| 16 | T-shirt yellow cotton large   |
| 18 | T-shirt yellow leather medium |
+----+-------------------------------+

Values in columns p.name, vo.value and v.name are less important. I need unique ids.

I fighting with GROUP BY, GROUP_CONCAT, MAX/MIN, etc... without results...

UPDATED: Added fiddle: https://www.db-fiddle.com/f/vNL6HWXjjx52cpFeCbR177/0

Question is, how to return products only one variant just like red cotton, red leather, blue cotton, blue leather, yellow cotton and yellow leather without looking at the variant "Sizes".

Answer 1

Keep this in mind:

When you JOIN tables, a temp table is built with all valid combinations (limited by WHERE and ON). In your sample query, that's what you got.

If you then add a GROUP BY to the query, it then tries to implode the rows into fewer rows. This is useful for getting COUNT(*) or SUM(...) (plus other "aggregates").

But the GROUP BY must include all the columns that are not being aggregated. ANY_VALUE is an ugly and problematic way around needing include all the columns. It is rarely "correct" to use that function.

Since you have not said (in words) what the query should do, I can't guess how to help you rewrite the queries. Providing the input and desired output might suffice. If you do that, try to minimize the number of input rows to show the problem in the output.