[thelist] How do I SELECT for the following...

Phil Turmel pturmel-webdev at turmel.org
Thu Feb 4 13:39:39 CST 2010

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 ...

MS SQL Server 2005: WITH cte ... SELECT ... FROM cte ...

PostgreSQL 8.4: WITH RECURSION cte AS ... SELECT ... FROM cte ...

I was very happy to see both window functions and recursion in PostgreSQL v8.4.



More information about the thelist mailing list