当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql categories and subcategories listing

问题描述:

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

网友答案:

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

网友答案:

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
分享给朋友:
您可能感兴趣的文章:
随机阅读: