why is this group_concat not working?
database, hierarchical-data, mysql, sql
Solution
Two things:
First: Change `GROUP_CONCAT(rs.category_id, ',')` to `GROUP_CONCAT(rs.category_id)`
The comma is the default separator, from the docs you can see that if you want to change the separator that would be something like `GROUP_CONCAT(rs.category_id SEPARATOR '|')`
Second: Remove the last: `GROUP BY rs.category_id`
If you `group by` each `category_id`, that mean each `category_id` is in it's own set and such the `group_concat` will only have one category per row.
http://sqlfiddle.com/#!2/24764/7
Problem
I don't understand why this GROUP_CONCAT is not working, as far as the outer query is concerned there are 3 rows returned so I want to group_concat by that but it doesn't like it... http://sqlfiddle.com/#!2/24764/3 ``` CREATE TABLE nested_category ( category_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, lft INT NOT NULL, rgt INT NOT NULL ); INSERT INTO nested_category VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4), (4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19),(7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13), (9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18); SELECT GROUP_CONCAT(rs.category_id, ',') FROM ( SELECT node.category_id, node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth FROM nested_category AS node, nested_category AS parent, nested_category AS sub_parent, ( SELECT node.category_id, node.name, (COUNT(parent.name) - 1) AS depth FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.name = 'PORTABLE ELECTRONICS' GROUP BY node.name ORDER BY node.lft )AS sub_tree WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt AND sub_parent.name = sub_tree.name GROUP BY node.name HAVING depth = 1 ORDER BY node.lft ) as rs GROUP BY rs.category_id ```