Say I have the following table:
TABLE: category
- category_id (PK)
- parent_id (FK)
- name
Given a value for category_id, how do I return the given category_id and all its descendants?
Asked
Active
Viewed 434 times
2
StackOverflowNewbie
- 39,403
- 111
- 277
- 441
-
Possible duplicate of: http://stackoverflow.com/questions/5291054/hierarchical-sql-problem – ChrisWue May 17 '11 at 22:54
1 Answers
3
If you want a single level, you'd do a SELECT on the condition category_id = id OR parent_id = id - but with MySQL, you cannot get a complete tree with a single query.
You can write a stored procedure to go through all of the intermediate results and pick up sub-children, but that really isn't very neat.
Instead, you can redesign your table. On the MySQL developer site, there is a nice article about how you can store hierarchical data in a table, and provides a design which is much more flexible than simply using a parent_id.
Paŭlo Ebermann
- 73,284
- 20
- 146
- 210
Michael Madsen
- 54,231
- 8
- 72
- 83
-
+1 for linking to that article, I was looking for it and going to post it, myself. – Rex Morgan May 17 '11 at 22:59