Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    5

    Unanswered: SQL Recursive query for tree style menu

    Hi all

    I'm a little stuck on a menu query that I'm needing to create. Any and all help would be greatly appreciated as I can't work this out.

    Information stored within the database is as follows, although the number of sub levels could easily grow :

    PageID - Title - ParentID
    1 - Title1 - 0
    2 - Title2 - 0
    3 - Title3 - 1
    4 - Title4 - 1
    5 - Title5 - 4

    Required outcome :

    [+] Title1
    .....[+] Title3
    .....[+] Title4
    ..........[+] Title5
    [+] Title2

    Many thanks in advance :-)

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Really this should be done in your presentation layer but since I've not written recursive SQL in a while I thought I'd give it a bash:

    Code:
    IF Object_ID('dbo.contents', 'U') IS NOT NULL
      BEGIN
        DROP TABLE dbo.contents
      END
    GO
    
    -- Create table
    CREATE TABLE dbo.contents (
       page_id   int     NOT NULL
     , title     char(6) NOT NULL
     , parent_id int         NULL
     , PRIMARY KEY (page_id)
     , FOREIGN KEY (parent_id) REFERENCES dbo.contents (page_id)
    )
    
    -- Test data
    INSERT INTO dbo.contents (page_id, title, parent_id)
      VALUES (1, 'Title1', NULL)
           , (2, 'Title2', NULL)
           , (3, 'Title3', 1)
           , (4, 'Title4', 1)
           , (5, 'Title5', 4)
    
    -- CTE
    ; WITH h AS (
      -- Grab all root items (those without a parent)
      SELECT page_id As base_page
           , page_id
           , title
           , parent_id
           , 0 As level
           , Convert(varchar(max), '[+]' + title) As display
      FROM   dbo.contents
      WHERE  parent_id IS NULL
        UNION ALL -- Recursive
          SELECT h.base_page
               , contents.page_id
               , contents.title
               , contents.parent_id
               , h.level + 1
               , Convert(varchar(max), Replicate('....', h.level + 1) + '[+]' + contents.title)
          FROM   dbo.contents
           INNER
            JOIN h
              ON h.page_id = contents.parent_id -- Join child to parent
    )
    -- Display result
    SELECT *
    FROM   h
    ORDER
        BY base_page
         , level
    
    -- Tidy up
    IF Object_ID('dbo.contents', 'U') IS NOT NULL
      BEGIN
        DROP TABLE dbo.contents
      END
    GO
    Any good to ya?
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2005
    Posts
    5
    Hi George
    As a 'bash', that's pretty damn good - thank you, it's perfect!
    Mucho Gracias!

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Do you understand the code provided? Don't be afraid to ask further questions
    George
    Home | Blog

Posting Permissions

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