Consider an employee table. Every employee has a chief (Chief_fk), except the chairman (.NULL). Now given an employee, give me the entire path from that employee to the chairman. The record based solution is far simpler than the set oriented solution:


USE EMPLOYEE
GO RECCOUNT()*RAND() && Get a random record
? Firstname+" "+Lastname
DO WHILE SEEK(Chief_fk,"Employee","emp_pk")
?? "\"+Firstname+" "+Lastname
ENDDO

Walter,

FWIW, T-SQL in SQL Server 2005 allows developers to build recursive queries through common table expressions:

WITH ResultSet (FirstName, LastName, Parent_PK_Value)
   AS (SELECT FirstName, LastName, Chief_FK  
       FROM Employee
          WHERE Emp_PK = <somekey>                                          -- anchor query
       UNION ALL 
          SELECT Employee.FirstName, Employee.LastName, Chief_FK            -- recursive query  
             FROM Employee
               INNER JOIN ResultSet ON Parent_PK_Value = Employee.Emp_PK)

SELECT * FROM ResultSet

I've run this on a small table (few thousand rows), I'm curious to try it on something larger.

Kevin