mysql categories and subcategories listing

1
votes

I need to create a query in mysql that will list the top level category and list any sub-category in it .The categories and subcategories are in same table. for eg.

id_category | id_parent | name

1 | 0 | category#1
2 | 1 | subcategory#1
3 | 1 | subcategory#2
4 | 2 | subsubcategory#1
5 | 0 | category#2
5 | 5 | subcategory#3
2
mysql

2 Answers

5
votes

You are trying to solve a common problem - how do you store and retrieve hierarchical data in a relational database?

The first thing that you should do is read this article at mysql.com, "Managing Hierarchical Data in MySQL" or here.

Right now, you are using the "adjacency list" model. It has several downsides, chief among them the fact that it is essentially impossible to write a query that will return you all the trees that you are storing.

You may want to consider switching to the "nested set" model, which is well explained on that same page.

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

-1
votes

Check it category & subcategory 

select m.id as cat_id ,m.name as category, e.name as sabcategory  from category e inner join category m on e.parent_id=m.id order by m.id