I'm dealing with the issue of trying to represent a tree in a table. In my case, it's nested Expense codes for an accounting system. I've set the table up with a self-referencing FK/PK relationship to get the parent of any row.
My problem is, what's the easiest and "cheapest" way to get the entire parentage of a given child row in the table. I'm willing to limit myself to some reasonable level of nesting -- say four ancestors for a row. I could do this in a single query if R:Base allowed multiple outer joins in the SELECT syntax, but it doesn't. My tendency would be to write a stored procedure to do sequential queries to get the parents, but I think this would be pretty expensive in terms of time. I recall a discussion of this issue some time ago. Anyone have any suggestions? -- Larry
