0

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

6
  • 1
    You can find some info using the keyword EAV (entity-attribute-value), it is an often misused (anti-)pattern, but if you cannot redesign it then you may need to join the "*variant*" tables multiple times, once for material, second time for size ...
    – jkavalik
    Commented Jan 23, 2023 at 14:17
  • Welcome to the DBA.SE community. In your question you wrote ...there are duplicates.... Could you be a bit more specific? And why do you have cotton in the product name, if you have the material in a separate table? That conflicts with the normalisation of the data. Hit the edit button and add more details, if possible. Thanks.
    – John K. N.
    Commented Jan 23, 2023 at 14:48
  • Welcome to the nightmares of EAV. Follow the tag here and in stackoverflow.com
    – Rick James
    Commented Jan 24, 2023 at 0:32
  • Guys, i updated the description
    – kicaj
    Commented Jan 24, 2023 at 11:53
  • @RickJames Are you suggesting to change the structure?
    – kicaj
    Commented Jan 24, 2023 at 11:55

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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