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 427 times
2
StackOverflowNewbie
- 37,253
- 107
- 262
- 431
-
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
- 71,139
- 18
- 140
- 206
Michael Madsen
- 53,013
- 7
- 71
- 82
-
+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