mysql query for same table category and subcategory with separated-CodePudding

This question already asked but that not solve my issue.

I have a table given below table name categories. I want to fetch all the category with separated >. Parent category data and Sub category data in same table.

categoryId   categoryName      categorytype  parentCategoryId   status
1            parent category       0              0                1
2            Sub category          1              1                1

So I want to fetch the data like this Output:

categoryId   categoryName                           categorytype  parentCategoryId   status
    1        parent category                           0              0                1
    2        parent category > Sub category            1              1                1

Only Sub category I want to add parent category as mentioned above.

I tried this query

SELECT * FROM categories c1 LEFT JOIN categories c2 ON c2.categoryId = c1.parentCategoryId;

Current output:

Please help me to solve this, thanks in advance.

CodePudding user response：

You query joins are OK, selecting right columns will do.

SELECT c1.categoryId, CONCAT((CASE WHEN c2.categoryId IS NOT NULL THEN CONCAT(c2.categoryName, ' > ') ELSE '' END), c1.categoryName) categoryName, c1.categoryType, c1.parentCategoryId, c1.status 
FROM categories c1 
LEFT JOIN categories c2 ON c2.categoryId = c1.parentCategoryId

It assumes your category structure is parent-sub two levels only.