Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40

    Unanswered: Selecting all items in a recursive relationship

    Hello

    I'm having trouble writing a query that references a join to a table with a recursive relationship in a SQL Server 2005 database.

    I have a list of products in Table1.
    Each product belongs to a Category.
    Categories are stored in Table2 which has a self-referencing relationship that forms a hierarchical list of categories and sub-categories.
    Like so...
    ||-ID-||-Name-||-Parent-||
    ||1 ||Cat1-----||Null-----||
    ||2 ||Cat2-----||Null-----||
    ||3 ||Cat3-----||1-------||
    ||4 ||Cat4-----||1-------||
    ||5 ||Cat5-----||2-------||
    ||6 ||Cat6-----||2-------||
    ||7 ||Cat7-----||3-------||

    What I'm trying to do is write a sproc that will pull down all products that belong to a particular category or any of it's sub-categories.

    I managed to write a sproc that could handle two levels of category by using union to join two queries together but I don't know how to handle an n-tier hierarchical structure.

    Can you point me in the right direction?

    Many Thanks

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    one way you can do it is:

    1. create a temp table that will store the relevant categories.
    2. then populate that temp table with the relevant categoryids by walking the tree in a while loop.
    3. then join your product table with the temp table and return the results.

  3. #3
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40
    Thanks for your response jezemine.
    I have actually just solved my problem using the new CommonTable Expressions for SQL Server 2005.

    References:
    Common Table Expressions
    Recursive queries in SQL 2005

    Very cool

Posting Permissions

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