MySQL2级,MySQL 2级MENU查询

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

There's only 1 table, of course :

idCategory | Title | idCategoryParent | DisplayOrder

1 | cat1 | NULL | 1

2 | sub-cat1 | 1 | 1

3 | sub-cat2 | 1 | 2

4 | cat2 | NULL | 2

5 | sub-cat3 | 4 | 1

6 | sub-cat4 | 4 | 2

7 | cat3 | NULL | 3

I'm looking for those results :

titleCat | titleSubCat | idCategory

cat1 | sub-cat1 | 1

cat1 | sub-cat2 | 1

cat2 | sub-cat3 | 4

cat2 | sub-cat4 | 4

cat3 | NULL | 7

OR something like that would be fine too :

cat1 | null | 1

cat1 | sub-cat1 | 1

cat1 | sub-cat2 | 1

etc..

I tried with something like :

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

FROM `test_category` as cat

RIGHT OUTER JOIN test_category as subcat ON cat.idCategory=subcat.idCategoryParent

Doesn't work bad but I struggle trying to order the records...

Here's the SQL Dump if you want to try it :

--

-- Table structure for table `test_category`

--

CREATE TABLE IF NOT EXISTS `test_category` (

`idCategory` int(11) NOT NULL AUTO_INCREMENT,

`idCategoryParent` int(11) DEFAULT NULL,

`title` varchar(20) NOT NULL,

`order` int(11) NOT NULL,

PRIMARY KEY (`idCategory`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

--

-- Dumping data for table `test_category`

--

INSERT INTO `test_category` (`idCategory`, `idCategoryParent`, `title`, `order`) VALUES

(1, NULL, 'cat1', 1),

(2, 1, 'sub-cat1', 1),

(3, 1, 'sub-cat2', 2),

(4, NULL, 'cat2', 2),

(5, 4, 'sub-cat3', 1),

(6, 4, 'sub-cat4', 2),

(7, NULL, 'cat3', 3);

Thanks! :)

解决方案

Your query is almost correct, but you need to use LEFT JOIN if you want categories with no subcategories, and you should select only first-level categories from the first table.

SELECT t1.title, t2.title, t1.idCategory

FROM

test_category t1

LEFT JOIN test_category t2 ON t2.idCategoryParent=t1.idCategory

WHERE t1.idCategoryParent IS NULL

ORDER BY t1.DisplayOrder, t2.DisplayOrder