SQL for hierarchical relationship

Question

I've a table where products are categorised based on hierarchical relationship like a tree structure. I'm having to select a category and all subcategories at any level. See the picture below:

e.g. i want an sql statement that when i query passing the id=11, it returns me (19,20,21,22,23,24,25,26)

Answer 1

There are several different ways to store heirarchical data in MySQL. Check out Bill Karwin's presentation that demonstrates four options.

• Adjacency List
• Path Enumeration
• Nested Sets
• Closure Table

You are using the adjacency list model for storing heirarchical data, but unfortunately this is the hardest model you could choose for querying subtrees.

Your options are:

• Change to a different model.
• Restrict queries to n levels deep.
• Use a stored procedure to query recursively. For more information about this, see Quassnoi's series of articles - Hierarchical queries in MySQL.