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".