Enhance : mysql recursive parent child query

Question

Desired Output: should have parent name with it's id as shown below

SELECT id,GetAncestry(id) as parents from Table1 where id = 7;

ID       PARENTS
7        zubuntu  id=6 , ubuntu id=3, linux id=2, os id=1

Desired 2nd Query:

SELECT id,GetAncestry(id) as parents from Table1 WHERE ID <= 7 ; 
'1', ''
'2', 'os id=1'
'3', 'linux id=2, os id=1'
'4', 'xbuntu id=3, linux id=2, os id=1'
'5', 'lbuntu id=3, linux id=2, os id=1'
'6', 'zbuntu id=3, linux id=2, os id=1'
'7', 'zubuntu 2  id=6 , ubuntu id=3, linux id=2, os id=1'

Current output:

SELECT id,GetAncestry(id) as parents from Table1 where id = 7;

ID  PARENTS
7   6,3,2,1

Second Query:

SELECT id,GetAncestry(id) as parents from Table1 WHERE ID <= 7 ;

'1', ''
'2', '  1'
'3', '  2 > 1'
'4', '  3 > 2 > 1'
'5', '  3 > 2 > 1'
'6', '  3 > 2 > 1'
'7', '  6 > 3 > 2 > 1'

Below is required mysql dump and function: CREATE TABLE Table1 (id int, name varchar(11), prent int) ;

INSERT INTO Table1
    (`id`, `name`, `prent`)
VALUES
    (1, 'os', NULL),
    (2, 'linux', '1'),
    (3, 'ubuntu', '2'),
    (4, 'xubuntu', '3'),
    (5, 'lubuntu', '3'),
    (6, 'zubuntu', '3'),
    (7, 'zubuntu 2', '6'),
    (8, 'suse', '2'),
    (9, 'fedora', '2'),
    (10, 'windowse', '1'),
    (11, 'windowse xp', '10'),
    (12, 'windowse 7', '10'),
    (13, 'windowse 8', '10'),
    (14, 'food', NULL),
    (15, 'dance', NULL) ;

Function:

    CREATE FUNCTION `GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN
    DECLARE rv VARCHAR(1024);
    DECLARE cm CHAR(1);
    DECLARE ch INT;

    SET rv = '';
    SET cm = '';
    SET ch = GivenID;
    WHILE ch > 0 DO
        SELECT IFNULL(`prent`,-1) INTO ch FROM
        (SELECT `prent` FROM Table1 WHERE id = ch) A;
        IF ch > 0 THEN
            SET rv = CONCAT(rv,cm,ch);
            SET cm = ',';
        END IF;
    END WHILE;
    RETURN rv;
END

You need something like [this](http://stackoverflow.com/questions/8833535/how-to-transform-a-mssql-cte-query-to-mysql). — user2989408, Feb 12 '14 at 17:17
Please see above 2 desired and current output.. Sorry, i was not clear enough earlier Thank you for a quick response — P Cing, Feb 12 '14 at 21:25

Enhance : mysql recursive parent child query

0 Answers0