Search code examples
mysqlselectleft-joincategories

mysql query for same table category and subcategory with separated

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

enter image description here

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: enter image description here

Please help me to solve this, thanks in advance.

Solution

  • 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.