recursive query across tables

[fideltfg]

hi all.

I'm trying to write a stored proceedure that will given a singal int value search within one table (categories) to find all its child categries then repeat the process untill it has all the children, grandchildren ect of the 'root' category. Then once it has those go to an other table ( products ) and pull all records that match the found catagorys in the first step sorted by catagory.

Can this be done? if so how or is this too much for SQL? should I rethink this and may be pull it apart in to many smaller queries?

the tables are as follows

Categories
	[CategoryID] [int] IDENTITY(1,1) NOT NULL,
	[CategoryName] [varchar](50) NOT NULL,
	[SortOrder] [int] NOT NULL,
	[ParentCategoryID] [int] NOT NULL

Products
	[ProductID] [int] IDENTITY(1,1)NOT NULL,
	[ProductDescription] [varchar](50) NOT NULL,
	[ProductCategoryID] [int] NOT NULL,
	[ProductSortOrder] [int] NOT NULL

the goal here is to producse a result set that can be stepped through to display a tree of products by category.

thanks everyone for any help you can give.

[gk53]

from SQL server something like. YOu can change selection criteria

;WITH Descendants AS (
 SELECT  [CategoryID]
                , [CategoryName]
                , 0 AS HLevel
 FROM Categories p  
 WHERE [CategoryID] = 1
 UNION ALL
 SELECT p.CategoryID
                , p.CategoryName
                , H.HLevel+1
 FROM Categories p  
 INNER JOIN Descendants H
 ON H.[CategoryID]=p.[ParentCategoryID]  
)  
SELECT [CategoryID], [CategoryName], HLevel, p.ProductID,p.ProductDescription
FROM Descendants d
left join Products p
on d.CategoryID=p.ProductCategoryID

[fideltfg]

sweet thanks. I turn my brain off now. Thats just about the point I'm at now with my efforts, thank you very much. I actualy wrote a solution in PHP, but really wanted it in sql and you saved me another day of frustration. koodos to you