title: MySQL Closure Table hierarchical database - How to pull information out in the correct order
body: <p>I have a MySQL database holding hierarchical data using the Closure Table method. A simple sample database create script follows the question. My issue at the moment is how do I pull data out of the database in the correct order? I'm currently using the following select statement.</p>
  
  <pre><code>SELECT `TreeData`.`iD`, `TreeData`.`subsectionOf`,
         CONCAT(REPEAT('-', `TreePaths`.`len`),`TreeData`.`name`),
         `TreePaths`.`len`,`TreePaths`.`ancestor`,`TreePaths`.`descendant`
  FROM `TreeData`
  LEFT JOIN `TreePaths` ON `TreeData`.`iD` = `TreePaths`.`descendant`
  WHERE `TreePaths`.`ancestor` = 1
  ORDER BY `TreeData`.`subsectionOrder`
  </code></pre>
  
  <p>It pulls the correct information but not in the correct order.</p>
  
  <p>The sample database create script with sample data.</p>
  
  <pre><code>-- Simple Sample
  SET FOREIGN_KEY_CHECKS=0;
  DROP TRIGGER IF EXISTS Tree_Insert;
  DROP TRIGGER IF EXISTS Tree_Update;
  DROP TABLE IF EXISTS TreePaths;
  DROP TABLE IF EXISTS TreeData;
  SET FOREIGN_KEY_CHECKS=1;
  
  
  CREATE TABLE `TreeData` (
      `iD`              INT NOT NULL,             -- PK
      `subsectionOf`    INT,                      -- Parent ID &amp; FK
      `subsectionOrder` INT,                      -- Oder of Subsections 
      `name`            NVARCHAR(500) NOT NULL,   -- Name for the entry
      PRIMARY KEY (`iD`),
      FOREIGN KEY (`subsectionOf`) REFERENCES TreeData(`iD`) ON DELETE CASCADE,
      INDEX(`name`)
  ) ENGINE = MYISAM;
  
  -- Trigger to update the EntryPaths table for new entries
  DELIMITER //
  CREATE TRIGGER `Tree_Insert` AFTER INSERT ON `TreeData` FOR EACH ROW 
  BEGIN
      INSERT INTO `TreePaths` (`ancestor`, `descendant`, `len`)
          SELECT `ancestor`, NEW.`iD`, len + 1 FROM `TreePaths`
              WHERE `descendant` = NEW.`subsectionOf`
              UNION ALL SELECT NEW.`iD`, NEW.`iD`, 0;
  END; //
  DELIMITER ;
  
  
  DELIMITER //
  CREATE TRIGGER `Tree_Update` BEFORE UPDATE ON `TreeData` FOR EACH ROW 
  BEGIN
      -- From http://www.mysqlperformanceblog.com/2011/02/14/moving-subtrees-in-closure-table/
      IF OLD.`subsectionOf` != NEW.`subsectionOf` THEN
          -- Remove the node from its current parent
          DELETE a FROM `TreePaths` AS a
          JOIN `TreePaths` AS d ON a.`descendant` = d.`descendant`
          LEFT JOIN `TreePaths` AS x
          ON x.`ancestor` = d.`ancestor` AND x.`descendant` = a.`ancestor`
          WHERE d.`ancestor` = OLD.`iD` AND x.`ancestor` IS NULL;
  
          -- Add the node to its new parent
          INSERT `TreePaths` (`ancestor`, `descendant`, `len`)
          SELECT supertree.`ancestor`, subtree.`descendant`, supertree.`len`+subtree.`len`+1
          FROM `TreePaths` AS supertree JOIN `TreePaths` AS subtree
          WHERE subtree.`ancestor` = OLD.`iD`
          AND supertree.`descendant` = NEW.`subsectionOf`;
      END IF;
  END; //
  DELIMITER ;
  
  
  CREATE TABLE `TreePaths` (
      `ancestor`      INT NOT NULL,
      `descendant`    INT NOT NULL,
      `len`           INT NOT NULL,
      PRIMARY KEY (`ancestor`, `descendant`),
      FOREIGN KEY (`ancestor`) REFERENCES TreeData(`iD`) ON DELETE CASCADE,
      FOREIGN KEY (`descendant`) REFERENCES TreeData(`iD`) ON DELETE CASCADE
  ) ENGINE = MYISAM;
  
  INSERT INTO `TreeData` VALUES(1, NULL, NULL, 'Root A');
  INSERT INTO `TreeData` VALUES(2, 1, 1, 'Item 1');
  INSERT INTO `TreeData` VALUES(3, 1, 2, 'Item 2');
  INSERT INTO `TreeData` VALUES(4, 1, 3, 'Item 3');
  INSERT INTO `TreeData` VALUES(5, 2, 2, 'Item 1 Sub Item 2');
  INSERT INTO `TreeData` VALUES(6, 2, 1, 'Item 1 Sub Item 1');
  INSERT INTO `TreeData` VALUES(7, 1, 3, 'Item 4');
  INSERT INTO `TreeData` VALUES(8, 4, 1, 'Item 3 Sub Item 1');
  INSERT INTO `TreeData` VALUES(9, 4, 2, 'Item 3 Sub Item 2');
  INSERT INTO `TreeData` VALUES(10, NULL, NULL, 'Root B');
  INSERT INTO `TreeData` VALUES(11, 10, 1, 'Item A');
  INSERT INTO `TreeData` VALUES(12, 10, 2, 'Item B');
  INSERT INTO `TreeData` VALUES(13, 10, 3, 'Item C');
  </code></pre>