Menu
Here's what I want:
attribute_name attribute_id attribute_value
--------------------------------------------------------------------
Appliances 16, 17, 18, 19 Washer, Dryer, Dishwasher, Microwave
Consoles 7, 3 PS3, XBox
Here's close to what I've got:
attribute_name attribute_id attribute_value
-------------------------------------------------
Appliances 16 Washer
Appliances 17 Dryer
Appliances 18 Dishwasher
Appliances 19 Microwave
Consoles 7 PS3
Consoles 3 XBox
...from this query:
SELECT a.name AS attribute_name,
av.attribute_value_id, av.value AS attribute_value
FROM attribute_value av
INNER JOIN attribute a
ON av.attribute_id = a.attribute_id
WHERE av.attribute_value_id IN
(SELECT attribute_value_id
FROM property_attribute
WHERE property_id = 1)
ORDER BY a.name;
I've had no success with GROUP_CONCAT. I don't even know what I want is possible.
759
The GROUP_CONCAT() function has a default length of 1024 characters, which is controlled by the global variable group_concat_max_len . If the joined values length is greater than the group_concat_max_len value, then the result string will be truncated.
The difference here is while CONCAT is used to combine values across columns, GROUP_CONCAT gives you the capability to combine values across rows. It's also important to note that both GROUP_CONCAT and CONCAT can be combined to return desired results.
The MySQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
The group_concat_max_len variable sets the result length in bytes for the GROUP_CONCAT() function to accommodate a potentially long string. (The default group_concat_max_len setting is 1024 bytes.)
Your existing query is returning everything you need to produce the concatenated columns. If you wrap your existing query in a subquery, you can GROUP_CONCAT() both columns and GROUP BY attribute_name:
SELECT
attribute_name,
GROUP_CONCAT(attribute_value_id) AS attribute_value_ids,
GROUP_CONCAT(attribute_value) AS attribute_values
FROM (
/* Wrap the body of your existing query in a subselect */
SELECT
a.name AS attribute_name,
av.attribute_value_id,
av.value AS attribute_value
FROM
attribute_value av
INNER JOIN attribute a
ON av.attribute_id = a.attribute_id
WHERE
av.attribute_value_id IN
(SELECT attribute_value_id
FROM property_attribute
WHERE property_id = 1)
) attr_groups
GROUP BY attribute_name
ORDER BY attribute_name;
SELECT group_concat(a.name, av.attribute_value_id, av.value)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
