RichardRotterdam Posted October 7, 2008 Share Posted October 7, 2008 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 Quote Link to comment Share on other sites More sharing options...
Vikas Jayna Posted October 7, 2008 Share Posted October 7, 2008 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 Quote Link to comment Share on other sites More sharing options...
fenway Posted October 7, 2008 Share Posted October 7, 2008 There are a variety of ways to store hierarchical dat. Quote Link to comment Share on other sites More sharing options...
RichardRotterdam Posted October 8, 2008 Author Share Posted October 8, 2008 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.