[SOLVED] tree structures

[RichardRotterdam]

I've encountered this situation a couple of times and usually i solved it by using some php.

So here is the situation.

 

When having to deal with tree structures sometimes it would be nice to have some sort of inheritance and getting all the child items.

 

for example when you have the following tree structure using 2 tables

categories and products

 

category1

-product1

-product2

+category2

-product3

-product4

+category3

-product5

-product6

 

let's say i want all the products in category1 including all the products in the sub categories.(in this case products 1 to 6)

 

what would be the best way to solve this. I am thinking about using a stored procedure for this and using some kind of loop in it. Would this be the best approach ?

I just want methods of solving it I dont want any full working queries or code i just want some hints.

 

ty

 

 

[Vikas Jayna]

You'll have to create a recursive procedure to find all the subcategories within a category and then use this list in the IN clause of the SQL query to find all the products within these categories

[fenway]

There are a variety of ways to store hierarchical dat.

[RichardRotterdam]

The storing part is easy using foreign keys

but fetching all lower child elements is a bit trickier.

 

I thought there might be some other neat way besides using a stored procedure but it seems to be the best way to go