SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru Majglow's Avatar
    Join Date
    Aug 1999
    Location
    B-Town
    Posts
    645
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Saving a tree structure in a database.

    Ok,

    In case this is relevant, I am using MS SQL.

    I have my site organized in a tree structure. Let's just say each row contains the following information.

    ID | Name | ParentID | Permissions

    Where the Permissions colomn contains permissions for the associated node in the tree.

    Now, when I load a node, I want to lead the node's parents, and so forth up to the root. Because not all permissions will be set in the permissions column for every node, and any permissions that aren't set need to be fetched from the parent node and so forth.

    So, I figure I need to select the necessary node & all the parents. Is there a way to do this in one query? What would be the best way to do it?
    Ohai!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    this article was published here just the other day, maybe it can help you

    http://www.sitepoint.com/article/1105

    you'll want to do this in a stored proc to make it as efficient as possible
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru Majglow's Avatar
    Join Date
    Aug 1999
    Location
    B-Town
    Posts
    645
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ooo... maybe I should actually start looking at the articles published here again

    I probably should really improve my SQL skills... specifically my MS SQL skills.

    What are some good ways to really learn this? Are there any good books or online resources?
    Ohai!

  4. #4
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Majglow,

    If you want to do it in a single query Microsoft have got an article on doing this for child descedants. The query is fairly easy to modify to get the parent ancestors instead.

    MSDN: Expanding Hierarchies

  5. #5
    SitePoint Guru Majglow's Avatar
    Join Date
    Aug 1999
    Location
    B-Town
    Posts
    645
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for the link...

    ok.. I really need to read up on MS Sql... I really don't know databases that well. I can barely follow the article. Does anybody know any good ways to learn how to "master" MS SQL? Book? Site? Anything?
    Ohai!


Bookmarks

Posting Permissions

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