I have this table:\[code\]CREATE TABLE `categories` ( `id` int(11) NOT NULL auto_increment, `category_id` int(11) default NULL, `root_id` int(11) default NULL, `name` varchar(100) collate utf8_unicode_ci NOT NULL, `lft` int(11) NOT NULL, `rht` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `category_id` (`category_id`), KEY `lft` (`lft`,`rht`), KEY `root_id` (`root_id`)) \[/code\]Based on this question: http://stackoverflow.com/questions/...der-tree-traversal-model-nested-set-into-a-ulThe difference is that I have many trees in one table. Each row has a foreign key representing its parent and its top parent: category_id and root_id. Also I have the lft and rht fields based on this example:http://articles.sitepoint.com/article/hierarchical-data-database/2Based on this rows:\[code\]INSERT INTO `categories` VALUES(1, NULL, NULL, 'Fruits', 1, 14);INSERT INTO `categories` VALUES(2, 1, 1, 'Apple', 2, 3);INSERT INTO `categories` VALUES(3, 1, 1, 'Orange', 4, 9);INSERT INTO `categories` VALUES(4, 3, 1, 'Orange Type 1', 5, 6);INSERT INTO `categories` VALUES(5, 3, 1, 'Orange Type 2', 7, 8);INSERT INTO `categories` VALUES(6, 1, 1, 'Pear', 10, 11);INSERT INTO `categories` VALUES(7, 1, 1, 'Banana', 12, 13);INSERT INTO `categories` VALUES(8, NULL, NULL, 'Eletronics', 1, 14);INSERT INTO `categories` VALUES(9, 8, 8, 'Cell Phones', 2, 3);INSERT INTO `categories` VALUES(10, 8, 8, 'Computers', 4, 9);INSERT INTO `categories` VALUES(11, 10, 8, 'PC', 5, 6);INSERT INTO `categories` VALUES(12, 10, 8, 'MAC', 7, 8);INSERT INTO `categories` VALUES(13, 8, 8, 'Printers', 10, 11);INSERT INTO `categories` VALUES(14, 8, 8, 'Cameras', 12, 13);\[/code\]How can I build an ordened list representing this tree?With the sql bellow:\[code\]SELECT c. * , (COUNT( p.id ) -1) AS depthFROM `categorias` AS pCROSS JOIN categories AS cWHERE (c.lftBETWEEN p.lftAND p.rht)GROUP BY c.idORDER BY c.lft;\[/code\]I got this result:
As you can see, I need to order by root_id too, so that I can generate the correct tree.Also, after get the tree, is there a way to order each node by name?