www.webdeveloper.com
Results 1 to 3 of 3

Thread: recursive query across tables

  1. #1
    Join Date
    Jan 2011
    Posts
    62

    recursive query across tables

    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

    Code:
    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.
    Visit my live dev site @ http://knowcode.ca

  2. #2
    Join Date
    Sep 2011
    Posts
    59
    from SQL server something like. YOu can change selection criteria


    Code:
    ;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

  3. #3
    Join Date
    Jan 2011
    Posts
    62
    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
    Visit my live dev site @ http://knowcode.ca

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles