On 02/04/2010 10:22 AM, Sarah Adams wrote: >> if you were to switch to the nested set model (characterized by the use >> of "lft" and "rgt" columns), things will go a lot easier for you, >> although i can't help you with it as i don't do the nested set model > > Here's a great article that got me started using a nested set model: > http://articles.sitepoint.com/article/hierarchical-data-database > > If you want to get more in-depth, try this one: > http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html > If you have the luxury of switching database engines, you can efficiently support the parent-child model: Oracle 8i: SELECT ... FROM ... STARTS WITH ... CONNECT BY ... http://www.oracle.com/technology/products/oracle9i/daily/oct04.html MS SQL Server 2005: WITH cte ... SELECT ... FROM cte ... http://msdn.microsoft.com/en-us/library/ms186243.aspx PostgreSQL 8.4: WITH RECURSION cte AS ... SELECT ... FROM cte ... http://www.postgresql.org/docs/8.4/interactive/queries-with.html I was very happy to see both window functions and recursion in PostgreSQL v8.4. HTH, Phil