We are building an application and client requirement is show child below each parent with a separator.
For eg: I have 2 tables as below
category: category_description:
id(PK) | parent_id id(fk) | name
-------|---------- -------|--------
1 | 0 1 | Fruits
2 | 1 2 | Apple
3 | 1 3 | Orange
4 | 0 4 | Veggies
5 | 4 5 | Tomatoes
6 | 4 6 | Cucumber
and so on ...
The result should show in a table as below
ID | Name
---|-----
1 | Fruits
2 | Fruits > Apple
3 | Fruits > Orange
4 | Veggies
5 | Veggies > Tomatoes
6 | Veggies > Cucumber
and so on...
Even if the client inserts randomly, the result should show as above.
I have tried to execute the following query
SELECT c.category_id AS category_id, c.parent_id, c.sort_order,
GROUP_CONCAT(cd.name SEPARATOR ' > ') AS name FROM
category c LEFT JOIN category_description cd ON c.category_id =
cd.category_id LEFT JOIN category_description cd1 ON cd1.category_id =
c.parent_id GROUP BY c.category_id, cd.name
But doesn't show the result as expected. Please let me know, where I am going wrong.
Hope, I could explain it better. Thanks in advance