I know the topic of self referencing tables has come up before, but I think this is a new question. I've got such a table, and right now I've got some terrible logic for getting the parent list (the logic is actually a recursive function in ASP, so there are god only knows how many round trips to the DB for any given lookup).Here's an approximate schema and some sample data:CREATE TABLE items (i int IDENTITY, title varchar(50), i_items int)insert into items (title,i_items) VALUES ('topOne',NULL)insert into items (title,i_items) VALUES ('topTwo',NULL)insert into items (title,i_items) VALUES ('topThree',NULL)insert into items (title,i_items) VALUES ('underOne',1)insert into items (title,i_items) VALUES ('thirdUnderOne',4)insert into items (title,i_items) VALUES ('underTwo',2)insert into items (title,i_items) VALUES ('thirdUnderTwo',6)insert into items (title,i_items) VALUES ('fourthUnderTwo',7)Now, what I'm looking for is a fully qualified title for any given node. If I lookup topOne, for instance, it should just be "topOne". If I lookup underOne, it should be "topOne / underOne", and looking up fourthUnderTwo should yield "topTwo / underTwo / thirdUnderTwo / fourthUnderTwo"I don't think the recursive SP limit will be an issue, so that is OK to use, but can anyone come up with a more clever approach to get the whole thing in one query rather than doing the recursive thing?Cheers-b 
Elegent parent list from self referencing table?
Posted - 2002-09-07 : 13:51:46
