Hi,
I had same issue.
Problem is, that parent level is not constant. So you cant do any
subselects.
You may use SP. Disadvantage : Perfomance with lots of records will
be poor.Advantage : it works well and no need overhead optimization.
(syntax not checked)
input (CATEGORY_NO )
for select CAT_PARENT from category
where category_no = :category_no
into :CAT_PARENT
do
begin
for
select items where category_no = category_no do
suspend;
category_no= CAT_PARENT;
end
Alternatively you may consider using some overhead fields/tables to
record sequences of child/parent/items to speed up performance.
Advantage : it will be faster, disadvantage : complex upate system and,
.possibly, complex algorithm.

myles@... wrote:

>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
>
>
>
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://firebird.sourceforge.net and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>

--

Pagarbiai / Best Regards,
Adomas Urbanavicius
+37068543336