title: Adjacency table to nested set conversion
body: <p>I need to convert an adjacency list to nested set in MySql. I have found only one resource over the internet to convert an adjacency list into a nested set using mysql(<a href="http://data.bangtech.com/sql/nested_set_treeview.htm" rel="nofollow">http://data.bangtech.com/sql/nested_set_treeview.htm</a>). The code is also on the same webpage.</p>
  
  <pre><code>CREATE TABLE test.Tree
  (emp CHAR(10) NOT NULL,
  boss CHAR(10));
  
  CREATE TABLE test.Personnel(
  emp CHAR(20) PRIMARY KEY,
  boss CHAR(20) REFERENCES Personnel(emp), 
  salary DECIMAL(6,2) NOT NULL
  );
  
  INSERT INTO test.Personnel VALUES ('jerry', 'NULL',1000.00);
  INSERT INTO test.Personnel VALUES ('Bert', 'jerry',900.00);
  INSERT INTO test.Personnel VALUES ('chuck', 'jerry',900.00);
  INSERT INTO test.Personnel VALUES ('donna', 'chuck',800.00);
  INSERT INTO test.Personnel VALUES ('eddie', 'chuck',700.00);
  INSERT INTO test.Personnel VALUES ('fred', 'chuck',600.00);
  
  
  INSERT INTO test.Tree
  SELECT emp, boss FROM test.Personnel;
  </code></pre>
  
  <p>I make the Tree table from Personnel table. Tree table has the boss-employee hierarchy. This is an adjacency list. To convert it to the nest set, I applied this code.</p>
  
  <pre><code>BEGIN ATOMIC
  DECLARE counter integer;
  DECLARE max_counter integer;
  DECLARE current_top integer;
  
  
  SET counter = 2;
  SET max_counter = 2 * (SELECT COUNT(*) FROM test.Tree);
  SET current_top = 1;
  
  INSERT INTO test.Stack
  SELECT 1, emp, 1, NULL
  FROM test.Tree
  WHERE boss IS NULL;
  
  DELETE FROM test.Tree
   WHERE boss IS NULL;
  
  WHILE counter &lt;=(max_counter - 2)
   LOOP IF EXISTS (SELECT * FROM test.Stack AS S1, test.Tree AS T1 
    WHERE S1.emp = T1.boss AND S1.stack_top = current_top)
   THEN
   BEGIN -- push when top has subordinates, set lft value
    INSERT INTO test.Stack
    SELECT (current_top + 1), MIN(T1.emp), counter, NULL
    FROM test.Stack AS S1, test.Tree AS T1
     WHERE S1.emp = T1.boss
     AND S1.stack_top = current_top;
  
     DELETE FROM test.Tree
     WHERE emp = (SELECT emp
     FROM test.Stack
     WHERE stack_top = current_top + 1);
  
     SET counter = counter + 1;
     SET current_top = current_top + 1;
     END
      ELSE
     BEGIN -- pop the stack and set rgt value
     UPDATE test.Stack
     SET rgt = counter,
     stack_top = -stack_top -- pops the stack
     WHERE stack_top = current_top
     SET counter = counter + 1;
     SET current_top = current_top - 1;
    END IF;
    END LOOP;
    END;
  </code></pre>
  
  <p>MySQL workbench shows several syntax errors which I could not remove. </p>
  
  <p>I am familiar with only very basic operations of mysql so could not debug the code on my own. How to remove all these errors? Plz Help.
  The second source I found to do the above operation is <a href="http://www.sqlservercentral.com/articles/Hierarchy/94040/" rel="nofollow">http://www.sqlservercentral.com/articles/Hierarchy/94040/</a> but the code is in T Sql and I don't have enough skills to translate it to MySQL.</p>