recursive query across tables
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
the goal here is to producse a result set that can be stepped through to display a tree of products by category.
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] [varchar](50) NOT NULL,
[SortOrder] [int] NOT NULL,
[ParentCategoryID] [int] NOT NULL
[ProductID] [int] IDENTITY(1,1)NOT NULL,
[ProductDescription] [varchar](50) NOT NULL,
[ProductCategoryID] [int] NOT NULL,
[ProductSortOrder] [int] NOT NULL
thanks everyone for any help you can give.