MYSQL Query for getting complete parent history from a child id

Question

Possible Duplicate:
Mysql recursion?

I am trying to get the complete parent information from a child id in a single query. Is it possible?

example :

    id  |  ts_name  |  parent_id
    1  |  admin  |  0
    2  |  user  |  0
    3  |  card  |  0
    4  |  pin  |  0
    5  |  venkat  |  0
    7  |  admin_sub  |  1
    18  |  admin_sub2  |  7
    19  |  admin_sub2.1  |  18
    23  |  admin_sub1  |  7
    27  |  admin_sub3  |  7
    30  |  ghgfh  |  26
    37  |  vbnvbn  |  7
    38  |  add_sub_1  |  1
    41  |  admin_sub2.2.1  |  19
    42  |  admin_sub2.1.1.1  |  20
    43  |  admin_sub2.1.1.1  |  19

So what I am trying to get is

when I give child id = 19 it should return: 18,7,1

How to do that?

Answer 1

you need to create a function in plsql language on the sql server (http://en.wikipedia.org/wiki/PL/SQL), I don't know the syntax well, but overall algorith would look like this:

function getParents(id):
    returns varchar(1000);
    IS result varchar(1000);
    begin
        while (id ! = 0)
            id = getParent(id);
            result = id + ', ';

        removeLastComma(result);
        return result;
    end

function getParent(parent):
    returns varchar(20);
    IS result varchar(20)
    begin
        select id into result from example_table where parent_id = parent
        return result
    end

removeLastComma(string):
     // cut last two characters of the string
     // return processed string

pl/sql tutorial: http://plsql-tutorial.com/plsql-functions.htm

Probably it won't work as a copy-paste solution but I hope it will be a useful starter.

Answer 2

Unfortunately, this can't be done in MySQL without either changing your table structure, or performing a number of queries.

See https://stackoverflow.com/a/4345944/574303