Get all levels on a tree structure

[BiggerBrother]

I don't know if this is possible, without iterating through every returned record. I have a table of categories. Each category has an id and a parent_id. If the parent is set to 0, then the category is top level. I need to be able to return all of the sub-categories below a parent category, regardless of the level. So if the parent is 1, and 2,3,4 are children, and 5,6,7 are children of 4, then i need to return 1,2,3,4,5,6 and 7.

Is this possible?

Ultimately, i need to be able to return all the products within those categories.

Many thanks

BB

 

[r937]

it is possible in mysql with your data design in one query only if there is a known maxi8mum number of levels

r937.com | rudy.ca

 

[BiggerBrother]

unfortunately, there could be any number of levels. I have knid of solved it, using iteration, and LOTS of or statements.

Many thanks

BB

 

[ericbrunson]

Search this forum for "adjacency" and you'll find some posts that talk about a) possible solutions and b) an alternative model for your data.