In SQL Server 7 there's an example of expanding hierarchies (under expanding hierarchies in the index) in books online that's probably in the 2000 docs too. It might need modifying a bit to do exactly what you want.
Adrian _____________________________________________ adrian marshall lead hat limited 28-29 great sutton street . london . ec1v 0ds http://www.leadhat.co.uk/ e: [EMAIL PROTECTED] p: 020 7566 9450 f: 020 7566 9458 ----- Original Message ----- From: "Peter Harrison" <[EMAIL PROTECTED]> To: "Dev@Lists. Cfdeveloper. Co. Uk" <[EMAIL PROTECTED]> Sent: Tuesday, October 08, 2002 9:44 AM Subject: [ cf-dev ] (OT) Select all records that are descendants of this record > Hi All > > I am trying to do a query on SQL Server 2000. I have a table with two > fields: MYTABLE_ID and PARENT_MYTABLE_ID (relates to self to create a > parent-child structure within the data). > > Example data: > > MYTABLE_ID,PARENT_MYTABLE_ID,[Comment] > > 1,NULL,(top-level - has no parent) > 2,1 > 3,1 > 4,NULL > 5,4 > 6,2 > 7,2 > 8,6 > 9,6 > > How can I do a SELECT on the table to return all the descendants for a given > ID? > > Given the above example data, if I want to find all descendant records for > MYTABLE_ID=1, it should return ID's: 2,3,6,7,8,9 > > I don't mind using a stored procedure if I have to. The numbers of records > will always be small, so performance is not a factor here. > > Oh how I miss Oracle at times like these. Thanks for any help. > > - Peter Harrison > > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > For human help, e-mail: [EMAIL PROTECTED] > -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
