I have a rather complex SQL problem, and I was hoping someone might have a
suggestion how to tackle this.

I have two tables. One is a 'Category' table, and the other is an 'Items'
table. Any one item can be a member of only one category. But any one
category can have zero, one or many items.

The Category also can be a member of another category (ie. A category can be
a child in a parent/child relationship with another category). Hence I have
a field in my category table called 'Parent' that references the unique ID
number of the Parent category record.

In affect, what I have is this:

Parent Category
Sub Category
Item
Item
Item

I have to write a query that returns all items that are members of a
requested Category. That's easy enough. But I also want it to return all
items that are members of the requested category's parent. And any parents
of that parent, and so on, until it runs out of levels.

The resulting Item list needs to be returned in sorted order, with the first
records being the 'most relevant' (ie. Members of the requested categories)
and then sorted as relevance is reduced.

I kinda need it returned like this:

ITEM_NO, ITEM_NAME, CATEGORY_NO

Here are the actual physical table DDLs.

CREATE TABLE CATEGORY (
CATEGORY_NO INTEGER NOT NULL,
CAT_PARENT INTEGER,
CAT_DESC VARCHAR (20) CHARACTER SET WIN1251 COLLATE WIN1251)

CREATE TABLE ITEM (
ITEM_NO INTEGER NOT NULL,
CATEGORY_NO INTEGER NOT NULL,
ITEM_NAME CHAR (40) CHARACTER SET WIN1251 COLLATE WIN1251)


If anyone has a suggestion, or some SQL that would do this that I can learn
from, it would be greatly appreciated.

Thanks
Myles


============================
Myles Wakeham
Director of Engineering
Tech Solutions US, Inc.
Scottsdale, Arizona USA
Phone (480) 451-7440
www.techsol.org