Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2008
    Posts
    23

    Unanswered: 3 level self join

    i need something exactly like the following example but with 3 levels self join : http://databases.about.com/od/sql/a/selfjoins.htm

    what i mean by 3 levels is that consider that relation like
    Director >>> Manager >>> Employee

    so the output will look like :
    dir1 mgr1 emp1
    dir1 mgr2 emp1
    dir1 mgr2 emp2
    dir2 null null
    dir3 mgr1 null

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So extend the sample code in the link you posted to an additional instance of the table. I don't see where your difficulty lies.

    If you need to deal with N-levels of organizational structure rather than just two or three, check out the Accumulator Table algorithm here for SQLSVR 2000 implementations: http://sqlblindman.googlepages.com/r...ngchildrecords

    ...or in SQLSVR 2005 you can use the new Common Table Expressions to plumb hierarchies.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by akkad
    i need something exactly like the following example but with 3 levels self join : http://databases.about.com/od/sql/a/selfjoins.htm
    much nicer example here: Categories and Subcategories

    applies to managers and subordinates just as easily
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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