title: Enhance : mysql recursive parent child query
body: <p>Enhance : mysql recursive parent child  query   </p>
  
  <p>Desired Output: should have <strong>parent name with it's id</strong> as shown below  </p>
  
  <pre><code>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
  </code></pre>
  
  <p>Desired 2nd Query: </p>
  
  <pre><code>SELECT id,GetAncestry(id) as parents from Table1 WHERE ID &lt;= 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'
  </code></pre>
  
  <p><strong>Current output:</strong></p>
  
  <p><em><code>SELECT id,GetAncestry(id) as parents from Table1 where id = 7;</code></em></p>
  
  <pre><code>ID  PARENTS
  7   6,3,2,1
  </code></pre>
  
  <p>Second Query:  </p>
  
  <p>SELECT id,GetAncestry(id) as parents from Table1 WHERE ID &lt;= 7 ; </p>
  
  <pre><code>'1', ''
  '2', '  1'
  '3', '  2 &gt; 1'
  '4', '  3 &gt; 2 &gt; 1'
  '5', '  3 &gt; 2 &gt; 1'
  '6', '  3 &gt; 2 &gt; 1'
  '7', '  6 &gt; 3 &gt; 2 &gt; 1'
  </code></pre>
  
  <p>Below is required mysql dump and function: 
      CREATE TABLE Table1
          (<code>id</code> int, <code>name</code> varchar(11), <code>prent</code> int) ; </p>
  
  <pre><code>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) ; 
  </code></pre>
  
  <p>Function: </p>
  
  <pre><code>    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 &gt; 0 DO
          SELECT IFNULL(`prent`,-1) INTO ch FROM
          (SELECT `prent` FROM Table1 WHERE id = ch) A;
          IF ch &gt; 0 THEN
              SET rv = CONCAT(rv,cm,ch);
              SET cm = ',';
          END IF;
      END WHILE;
      RETURN rv;
  END
  </code></pre>