Table Walk using parent/child foreign keys

[Jack Baker]

Hi

We are using version 5.0.81 of MySQL database and need to do a tale walk using parent/child keys.

We have a table: inventory_categories

INVENTORY_CATEGORY_ID int(15)
PARENT_INVENTORY_CATEGORY_ID int(15)
INVENTORY_CATEGORY_NAME varchar(30)
INVENTORY_CATEGORY_DESCRIPTION varchar(80)
INVENTORY_CATEGORY_TYPE_ID int(15)
INVENTORY_CATEGORY_ORDER int(10)
GRAPHICS_FILE_NAME varchar(80)
DELETED_INDICATOR char(1)
CREATED_BY int(15)
CREATION_DATE datetime
LAST_UPDATED_BY int(15)
LAST_UPDATE_DATE datetime

Where each category can have one or more sub-catgories and each sub-catgory can have one or more sub-categories, etc.

We need a quick way to traverse the tables to find all sub-categories (inventory_category_id) for a given inventory_category_id.

We are using JSP with embedded SQL. We obviously would like to accomplish as much of this within the query itself.

Thanks in advance for your help!

Jack