CODEWITHSUNDEEP
mysqldistinctgroup-concat
hovado
hovado

Reputation: 4948

MySQL: DISTINCT in GROUP_CONCAT remove same values (not duplicates)

I have two tables: items and item_params. Items table is usual list of items and item_params is table where there are several param names and its values.

+-----------------------------------------+
| id | item_id | param_name | param_value |
+-----------------------------------------+
|  1 |       1 | Width      | 80 cm       |
|  2 |       1 | Length     | 80 cm       |
|  3 |       1 | Height     | 110 cm      |
+-----------------------------------------+

I need one request so my query looks like:

SELECT i.*,
    GROUP_CONCAT(DISTINCT ip.param_name SEPARATOR '|') AS param_names,
    GROUP_CONCAT(DISTINCT ip.param_value SEPARATOR '|') AS param_values
FROM items i
LEFT JOIN item_params ip ON ip.item_id = i.item_id
WHERE i.item_id = 1
LIMIT 1

To avoid getting repeated value I use DISTINCT, but if param_value has the same values for different param_name it return only 80 cm|110 cm.

How can I achieve to get unique param_name with all its param_value regardless same values?

Upvotes: 1

Views: 1380

Answers (1)

axiac
axiac

Reputation: 72226

The expressions in the SELECT clause of a query are independent, each of them is computed using only the data involved in the expression.

What you probably need is to glue the param names with their corresponding param values then pass them to GROUP_CONCAT and DISTINCT.

Something like this:

SELECT i.*,
   GROUP_CONCAT(DISTINCT CONCAT(param_name, ':', param_value) SEPARATOR '|') AS params,
FROM items i
LEFT JOIN item_params ip ON ip.item_id = i.item_id
WHERE i.item_id = 1
GROUP BY i.item_id

This query should produce Width:80 cm|Length: 80 cm|Height: 110 cm and you can split this string and recover the param names and values in the client code.

Upvotes: 1

Related Questions