Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2007
    Posts
    64

    Thumbs down Unanswered: Hierarchichal query in SQL Server 2000

    Hi All,

    How to implement a hierarchical query in SQL Server 2000.

    Example ; I have an Oracle Query as below

    SELECT LEVEL,employee_id, manager_id, first_name, last_name
    FROM employee
    START WITH employee_id = 1
    CONNECT BY prior employee_id = manager_id;


    I need to get the equivalent of this query in SQL Server 2000 .

    In SQL Server 2005 i can achieve this using COMMON TABLE EXPRESSION .

    Is there any way to implement this in SQL Server 2000

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sushma007
    Is there any way to implement this in SQL Server 2000
    not easily, no

    if there is some maximum number of levels to the hierarchy (e.g. never more than eleven levels deep, from top executive to lowliest peon), then you can write a query with that number of LEFT OUTER JOINs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can do this efficiently using a loop. Check out this link, and then I will answer any other questions you have:
    http://sqlblindman.googlepages.com/r...ngchildrecords
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Aug 2007
    Posts
    64
    Hi Blindman,

    I am unable to open the link u provided .

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no error, url works just fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2005
    Posts
    122
    Here is a little tutorial i wrote some time ago that gives you what you want.

    http://vbforums.com/showthread.php?t=366078

Posting Permissions

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