View Full Version : Issue querying a category and subcategory

09-19-2006, 08:09 PM
I have a table in a database (store_category) that lists categories and subcategories. The subcategories have a field with the parent category's id in it. So something like:

id | name | parent
1 Dog 0
2 Puppy 1
3 Cat 0
4 Kitty 3

I then have a products table and in that table, each row has a field for the id of the category it's in.

The problem I'm having is selecting something like all of the products that are in both the Dog category and it's subcategories (in this case, Puppy). I can select a single category (parent or sub) just fine, but it's if I select only the parent and want to also include the products in it's subcategory that I run in to issues.

09-19-2006, 08:30 PM
this article (http://sqllessons.com/categories.html) explains the use of the adjacency list method quite nicely. there is also a link to Joe Celko's nested set method as well as Modified Preorder Tree Traversal.

A great resource.