We do not have the option of moving to SQL 2005 quickly. Also, the hierarchy is about 10 deep. There is no built in limit, but that is about how deep they go on average. Things change frequently, however, and even though a product belongs to one class, it may be shelved with another class for a time, and it needs to be associated with that class for that time. This is taken care of with a tempClassPrntID (not included in the table design). I did not put this in originally to keep things more simple.

Thank you for any help.




>You'll have to move to SQL 2005 in order to accomplish this with a simple SQL statement. How deep is you hierarchy? Does it have a limited # of levels?
>
>>In a catalog db, where each product has a classID, and each class has a parent ClassID, up numerous levels, and one must generate a list of all parent ClassIDs for a specific product in the quickest, most efficient manner, what would that manner be? I have tried cursors (not fast), while loops, etc. but these take longer that 2 minutes to run (the list is long and a little complicated). I have run through the index tuner with a load file, but the indexes seem to be OK. Consider the tables as the following:
>>
>>CREATE TABLE [dbo].[Class] (
>> [ClassID] [int] IDENTITY (1, 1) NOT NULL ,
>> [ClassDescrp] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>> [PrntClassID] [int] NULL
>>) ON [PRIMARY]
>>GO
>>
>>CREATE TABLE [dbo].[Product] (
>> [ProductID] [int] IDENTITY (1, 1) NOT NULL ,
>> [ProductDescrp] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>> [ClassID] [int] NOT NULL
>>) ON [PRIMARY]
>>GO
>>
>>ALTER TABLE [dbo].[Class] WITH NOCHECK ADD
>> CONSTRAINT [PK_Class] PRIMARY KEY CLUSTERED
>> (
>> [ClassID]
>> ) ON [PRIMARY]
>>GO
>>
>>ALTER TABLE [dbo].[Product] WITH NOCHECK ADD
>> CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
>> (
>> [ProductID]
>> ) ON [PRIMARY]
>>GO
>>
>>ALTER TABLE [dbo].[Class] ADD
>> CONSTRAINT [FK_Class_Class] FOREIGN KEY
>> (
>> [PrntClassID]
>> ) REFERENCES [dbo].[Class] (
>> [ClassID]
>> )
>>GO
>>
>>ALTER TABLE [dbo].[Product] ADD
>> CONSTRAINT [FK_Product_Class] FOREIGN KEY
>> (
>> [ClassID]
>> ) REFERENCES [dbo].[Class] (
>> [ClassID]
>> )
>>GO
>>
>>I would like a simple select statement to do the task, but am not sure how to put that together.
>>All help is greatly appreciated!!