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 May 17, 2011 at 22:50
StackOverflowNewbie
40.9k114 gold badges295 silver badges454 bronze badges
-
Possible duplicate of: stackoverflow.com/questions/5291054/hierarchical-sql-problemChrisWue– ChrisWue2011-05-17 22:54:00 +00:00Commented May 17, 2011 at 22:54
Add a comment
|
1 Answer
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.
1 Comment
Rex Morgan
+1 for linking to that article, I was looking for it and going to post it, myself.