Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    22

    Unanswered: Selecting a folder hierarchy

    Apologies for the vague thread title, what I am about to describe in somewhat difficult to summarise.

    I am looking to run a single stored procedure that will retrieve a hierarchy based on the following table structures:

    Folders
    Id (int)
    Guid (uniqueidentifier)
    Description (varchar(200))
    ParentFolderId (int)

    FolderItems
    Id (int)
    Guid (uniqueidentifier)
    Description (varchar(200))
    FolderId (int)
    Active (bit)

    So this makes a little more sense, here is some sample data:

    Folders
    1, <uid>, Folder1, null
    2, <uid>, Folder2, null
    3, <uid>, Folder1_1, 1
    4, <uid>, Folder2_1, 2
    5, <uid>, Folder1_1_1, 3
    6, <uid>, Folder2_1_1, 4
    7, <uid>, Folder2_2, 2
    8, <uid>, Folder2_2_1, 7
    9, <uid>, Folder2_2_1_1, 8
    10, <uid>, Folder3, null
    11, <uid>, Folder3_1, 10
    12, <uid>, Folder3_1_1, 11
    13, <uid>, Folder3_1_1_1, 12


    FolderItems
    1, <uid>, Wibble, 3, true
    2, <uid>, Wobble, 2, true
    3, <uid>, Foobar, 6, false
    4, <uid>, Widget, 9, false
    5, <uid>, WingDing, 13, false

    This sample data gives me the following hierarchy (folders in red, folder items in green, inactive items in green italic)

    • Folder1
      • Folder_1_1
      • Wibble
        • Folder_1_1_1
    • Folder2
      • Folder2_1
      • Wobble
        • Folder2_1_1
          • Foobar
      • Folder2_2
        • Folder2_2_1
          • Folder2_2_1_1
          • Widget
    • Folder3
      • Folder3_1
        • Folder3_1_1
          • Folder3_1_1_1
          • WingDing




    I need to get a result set that contains:

    All folders that contain a sub folder that contains either a sub folder or an active item (at some level). I also need the result set to be order by hierarchy, with the root folders first.

    That probably didn't make sense, so from the example above I need to get:

    Folders Result Set
    Folder1, null - Included as it contains Folder1_1 and item Wibble
    Folder2, null - Included as it contains Folder2_1 and item Wobble
    Folder1_1, 1 - Included as it contains item Wibble
    Folder2_1, 2 - Included as it contains item Wobble
    Folder3 - Included as it contains an active item (in a sub folder down a few levels)
    Folder3_1 - Included as it contains an active item (in a sub folder down a few levels)
    Folder3_1_1 - Included as it contains an active item (in a sub folder down a few levels)
    Folder3_1_1_1 - Included as it contains an active item

    Here are the items no included in the result set and the reasons why:

    5, <uid>, Folder1_1_1, 3 - Excluded as it does not contains any items
    6, <uid>, Folder2_1_1, 4 - Excluded as the foobar item it contains is not active
    7, <uid>, Folder2_2, 2 - Excluded as there are no active items in any sub folder below it
    8, <uid>, Folder2_2_1, 7 - Excluded as there are no active items in any sub folder below it
    9, <uid>, Folder2_2_1_1, 8 - Excluded as the Widget item is not active

  2. #2
    Join Date
    Jan 2003
    Location
    British Columbia
    Posts
    44
    Which version of SQL Server? Both 2005 and 2008 have new functionality that makes using hierarchy data easier to traverse.

Posting Permissions

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