Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Feb 2005
    Posts
    76

    Unanswered: tree structures...

    Does anyone know any good links for SQL tree structures and example queries and stuff... I cant really find anything part from the standard example of emplyee, boss, salary which explains how to create the tree table...(dun dis bit) I did notice a book but I live in a little village so cant go get it till wekend?

    I'm desperate, reli need to work out how too do this.....

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Rudy, that link does a good job of setting up the problem, but I disagree with the solutions it presents. Neither a series of left-joins nor a true recursive procedure is a good method of handling recursive data in SQL. The first is not robust, and the second is not efficient. That website did not even touch upon the accumulator method of spanning trees.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the accumulator method of spanning trees?? sheeeit, if i knew what that meant, i might add something to the article (but don't hold your breath)

    regarding robustness, i think you probably missed the comments in the article about how if you are going down more than 4 levels for a web site navigation scheme, you have an information architecture problem

    left joins to 15 levels have been tested and found to perform splendidly

    is there something else i don't understand about the word "robust"? is that some kind of secret microsoft buzzword?

    and if you disagree with the article, for heaven's sake, man, write your own article and i'll post it -- i've offered you the opportunity before, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Did you write that article? I didn't think it was one of your websites.

    I am starting up a website with some sql content using Google's website feature. I should publicize it shortly and will include an article on the accumulator method.

    Note that the term "accumulator method" appears to have been coined by myself, though I certainly don't lay claim to the algorithm. If there is a more accepted term available then I will use that instead.

    Here is the standard article I post:
    -------------------------------------------------------
    The most flexible and robust method of storing hierarchical data in a database is to use a table with a recursive relationship. In this design, each record has an associated parent record ID that indicates its relative place in the hierarchy. Here is an example:

    CREATE TABLE [YourTable]
    ([RecordID] [int] IDENTITY (1, 1) NOT NULL ,
    [ParentID] [int] NULL)

    The challenge is to find a way to return all the child records and descendants for any given parent record.

    While recursion is supported within SQL Server, it is limited to 32 nested levels and it tends to be ineffecient because it does not take full advantage of SQL Server's set-based operations.

    A better algorithm is a method I call the "Accumulator Table".

    In this method, a temporary table is declared that accumulates the result set. The table is seeded with the initial key of the parent record, and then a loop is entered which inserts the immediate descendants of all the records accumulated so far which have not already been added to the table.

    Here is some skeleton code to show how it works:

    --This variable will hold the parent record ID who's children we want to find.
    declare @RecordID int
    set @RecordID = 13

    --This table will accumulate our output set.
    declare @RecordList table (RecordID int)

    --Seed the table with the @RecordID value, assuming it exists in the database.
    insert into @RecordList (RecordID)
    select RecordID
    from YourTable
    where YourTable.RecordID = @RecordID

    --Add new child records until exhausted.
    while @@RowCount > 0
    insert into @RecordList (RecordID)
    select YourTable.RecordID
    from YourTable
    inner join @RecordList RecordList on YourTable.ParentID = RecordList.RecordID
    where not exists (select * from @RecordList CurrentRecords where CurrentRecords.RecordID = YourTable.RecordID)

    --Return the result set
    select RecordID
    from @RecordList

    This method is both flexible and efficient, and the concept is adaptable to other hierarchical data challenges.

    For a completely different method of storing and manipulating hierarchical data, check out Celko's Nested Set model, which stores relationships as loops of records.

    http://www.intelligententerprise.com...stid=145525%5D
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's terrific -- please be sure and let me know when you publish, and i'll definitely add a link to yours from my article

    what's "Google's website feature"?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by STUCK1234
    I did notice a book but I live in a little village so cant go get it till wekend?

    Where's the village?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by r937
    "Google's website feature"?
    I've gotten a g-mail account, which comes with many additional online features including an excellend publishable calendar, a spreadsheet, a word processor, and a website page creator.
    Since your are curious, here is what I have so far:
    http://sqlblindman.googlepages.com/main
    I'll be copying over a lot more content from scattered files as I have the time.

    Check out the Chess Club link and you'll see an example of a published google calendar.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    damn, that's nice

    an extremely pleasant design, and the promise of more goodness to come

    please let me know when you add more articles
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by blindman
    Since your are curious, here is what I have so far:
    http://sqlblindman.googlepages.com/main
    I love this quote you have on your site:

    Physics is to math what sex is to masturbation.
    Richard Feynman

    YES!!! as a former physicist I can vouch for that one.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Richard Feynman rules. I bought a poster of him that was used for Apple's "Think Different" campaign, and have it hanging above my computer (a PC, ironically).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Anyone that appreciates Richard Feynman is Ok in my book. He was one wickedly good samba drummer, and not a bad dancer either!

    -PatP

  14. #14
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    the really amazing thing about him for me is what a fresh outlook he brought to topics in science, not just physics. However his series of lectures on physics are amazing. Most intro physics textbooks are really dull, and don't do the subject matter justice. His are excellent! They are the only textbooks I have from grad school that I would even consider re-reading.

    plus he was pretty bright

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I most admire his scientific approach to picking up girls.

    I have a recording of some of his lectures on CD, and it is surprising to hear his think Bronx accent. Here's this grey-haired nobel physics laureate, and he sounds like a common street thug. Very amusing.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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