Products multiple variants
0
votes
1
answer
135
views
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".
Asked by kicaj
(123 rep)
Jan 23, 2023, 02:03 PM
Last activity: Jan 24, 2023, 04:49 PM
Last activity: Jan 24, 2023, 04:49 PM