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!!