help with a recursive SQL query

I am having a problem with what I guess is a recursive SQL query.

The simplified version of the problem is I have a table that consists of two columns of numbers, the record_id and the parent_record_id. I would like to build a string that consists of the full chain of ancestors, something like

record::parent1::parent2:: ... ::parentN

What is the most efficient way to do this? I don't know how many ancestors a given record will have; I expect it will be different for each record.

thanks,
Jeff Received on Thu Nov 10 2005 - 18:12:12 CST