Hierarchical table - how to get paths of the items [linked lists in MySQL]

Question

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 NULLs 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 based on the linked list technique.

UPDATE: I would like to see a solution that will not use recursion or constructs like for and while. The above method for finding paths doesn't use any loops or functions. I want to find a solution in the same logic. Or, if it's impossible, please try to explain why!

Answer 1

Define the getPath function and run the following query:

select id, parent, dbo.getPath(id) as path from t_hierarchy 

Defining the getPath function:

create function dbo.getPath( @id int)
returns varchar(400)
as
begin
declare @path varchar(400)
declare @term int
declare @parent varchar(100)
set @path = ''
set @term = 0
while ( @term <> 1 )
begin
   select @parent = parent from t_hierarchy where id = @id
   if ( @parent is null or @parent = '' or  @parent = @id )
        set @term = 1
   else
        set @path = @path + @parent   
   set @id = @parent     
end
return @path
end