Edit Question - KnowledgeHub

Title Body

I have a hierarchical table in MySQL: parent field of each item points to the id field of its parent item. For each item I can get the list of all its parents [regardless the depth] using the query described here . With GROUP_CONCAT I get the full path as a single string: SELECT GROUP_CONCAT(_id SEPARATOR ' > ') FROM ( SELECT @r AS _id, ( SELECT @r := parent FROM t_hierarchy WHERE id = _id ) AS parent, @l := @l + 1 AS lvl FROM ( SELECT @r := 200, @l := 0 ) vars, t_hierarchy h WHERE @r <> 0 ORDER BY lvl DESC ) x I can make this work only if the id of the item is fixed [it's 200 in this case]. I want to do the same for all rows: retrieve the whole table with one additional field ( path ) which will display the full path. The only solution that comes to my mind is to wrap this query in another select, set a temporary variable @id and use it inside the subquery. But it doesn't work. I get NULL s in the path field. SELECT @id := id, parent, ( SELECT GROUP_CONCAT(_id SEPARATOR ' > ') FROM ( SELECT @r AS _id, ( SELECT @r := parent FROM t_hierarchy WHERE id = _id ) AS parent, @l := @l + 1 AS lvl FROM ( SELECT @r := @id, @l := 0 ) vars, t_hierarchy h WHERE @r <> 0 ORDER BY lvl DESC ) x ) as path FROM t_hierarchy P.S. I know I can store the paths in a separate field and update them when inserting/updating, but I need a solution ba

Tags (comma-separated) Cancel