For every category show category_id, depth (1 for the top category, 2 for its
children, and so on) and path: the category names from the top category down to it, joined
with >, such as All Products > Lifestyle > Sports.
End your query with ORDER BY path.
| categories |
|---|
| category_id (PK) |
| category_name |
| parent_category_id (FK) |
For every category show category_id, depth (1 for the top category, 2 for its
children, and so on) and path: the category names from the top category down to it, joined
with >, such as All Products > Lifestyle > Sports.
End your query with ORDER BY path.
| categories |
|---|
| category_id (PK) |
| category_name |
| parent_category_id (FK) |
| category_id | depth | path |
|---|---|---|
| 9 | 1 | All Products |
| 10 | 2 | All Products > Gadgets & Media |
| 2 | 3 | All Products > Gadgets & Media > Books |
| 1 | 3 | All Products > Gadgets & Media > Electronics |
| 13 | 4 | All Products > Gadgets & Media > Electronics > Smart Home |
| 12 | 2 | All Products > Home & Family |
| 7 | 3 | All Products > Home & Family > Grocery |
| 4 | 3 | All Products > Home & Family > Home & Kitchen |
| 8 | 3 | All Products > Home & Family > Toys |
| 11 | 2 | All Products > Lifestyle |
| 6 | 3 | All Products > Lifestyle > Beauty |
| 3 | 3 | All Products > Lifestyle > Clothing |
| 5 | 3 | All Products > Lifestyle > Sports |
Run query to see output