MySql select query for same table category and subcategory parent under child-CodePudding

This question already asked but that not solve my issue.

I have a table given below table name categories, here parent and child are inserted same table. I want to fetch all the child category under parent.

My table:

categoryId   categoryName      categorytype  parentCategoryId   status
1            cars                   0              0                1
2            honda city             1              1                1
3            Medical                0              0                1
4            Cancer                 1              4                1
5            bmw                    1              1                1

So I want to fetch the data like this Output:

categoryId   categoryName        parentCategoryId
1            cars                        0       
2            honda city                  1       
5            bmw                         1      
3            Medical                     0       
4            Cancer                      4

What am trying to do is car is a parent under this all cars name will be listed.

This is the query I tried:

SELECT * FROM categories c1 left join categories c2 on c2.categoryId = c1.parentCategoryId;

CodePudding user response：

This one could work

SELECT c1.* 
FROM categories c1 
LEFT JOIN categories c2 on c2.categoryId = c1.parentCategoryId
ORDER BY COALESCE(c2.categoryId, c1.categoryId), c1.categoryId

Edit

If root category is fixed 0, it can be simpler without JOIN

SELECT *
FROM categories
ORDER BY CASE WHEN parentCategoryId = 0 THEN categoryId ELSE parentCategoryId END, categoryId