CODEWITHSUNDEEP
mysqlsql
StackOverflowNewbie
StackOverflowNewbie

Reputation: 40653

MySQL - how to query a tree?

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?

Upvotes: 2

Views: 447

Answers (1)

Michael Madsen
Michael Madsen

Reputation: 55009

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.

Upvotes: 3

Related Questions