Larry, If you need multiple outer joins, just use views on views for however many levels you need.
Create vie v1 as select .... from mytable t1 left outer mytable t2 on primaryKey = ForeignKey Create vie v2 as select ... from v1 left outer joing mytable t3 on ....... You might also want to consider using Unions to get the information. Troy -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Lawrence Lustig Sent: Sunday, July 11, 2004 9:43 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Representing a tree in a table. 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
