sql - MySQL 2 level MENU Query -

I'm trying to make a MySQL request to retrieve a 2 level menu (parent and children) ...

Of course, there is only 1 table:

  idcategory | Title | IdCategoryParent | DisplayOrder 1 | Cat 1 | Faucet 1 2 | Sub-cat 1 | 1 | 1 3 Sub-cat 2 | 1 | 2 4 | Cat 2 Faucet 2 5 | Sub-cat 3 | 4 | 1 6 | Sub-cat 4 | 4 | 2 7 | Cat 3 Faucet 3  

I am looking for those results:

  titleCat | Title | Shabeq | Id category cat 1 | Sub-cat 1 | 1 cat 1 | Sub-cat 2 | 1 cat 2 | Sub-cat 3 | 4 Cat 2 | Sub-cat 4 | 4 Cat 3 | Faucet 7  

Or anything would be okay:

  cat1 | Empty | 1 cat 1 | Sub-cat 1 | 1 cat 1 | Sub-cat 2 | 1 etc.  

I tried to do something like this:

  SELECT subcat.title as catTitle, parentTitle as cat.title, subcat.idCategory Cat.idCategoryParent  

does not work as test_category parasite for cat-right as part of catIdCategory, in the form of 'test_category` from Subcat.idCategoryParent, But I'm trying to order the record ...

Here is a SQL Dump, if you want to try it:

  - - Table structure for the table 'Test_category` - Create a table if 'test_category` is not IDCGrtti (11) tap AUTENCRAMENT, IDCARTIPIT (11) default faucet,' title 'form (20) tap,' order 'int (11) faucet, primary (`IDC category`) )) Engine No = Indo DB Default Charge = UTF 8 AUTONCRM = 8; --Data dumping for table `test_category` -` test_category` (`idcgree`,` idc district`, `title`, 'order'] value (1, zero, 'cat 1', 1), ( 2, 1, 'sub-cat 1', 1), (3, 1, 'sub-cat 2', 2), (4, faucet, 'cat 2', 2), (5, 4, 'sub -Cat 3 ', 1), (6, 4,' sub-cat 4 ', 2), (7, tap,' cat 3 ', 3);  

Thanks! :)

Your query is almost correct, but you need to use LEFT JOIN If you do not want categories with any subcategories, and you should select only first-class categories from the first table.

  SELECT t1.title, t2.title, t1.idcategory test_category t1 LEFT JOIN to test_category t2 at t2.idCategoryParent = t1.idCategory where t1.idCategoryParent IS is T1 by TL order. DisplayApplacer, T2 Display Order