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 2000Is there any way to implement this in SQL Server 2000not 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|||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/returningchildrecords|||Hi Blindman,

I am unable to open the link u provided .|||http://sqlblindman.googlepages.com/returningchildrecords
Are you getting an error?|||no error, url works just fine|||Here is a little tutorial i wrote some time ago that gives you what you want.

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