On 28 July 2011 10:39, Arno Kuhl <arno@xxxxxxxxxxxxxx> wrote: > Arno Kuhl wrote: >> Not strictly a php issue but it's for a php app, hope that counts >> (plus I haven't had much joy googling this) >> >> I have a table with an id and a parentid. >> If it's a top-level record the parentid is 0, otherwise it points to >> another record, and if that record isn't a top-level record its >> parentid points to another record, etc (a linked list). >> >> Is there a single select that will return the complete list of parentids? >> Or do I have to iterate selecting each parent record while parentid> >> 0 and build the list entry by entry? > > Little difficult to answer what you don't say what you are using as a > database. > Recursive queries are now possible on many databases, except I think for > MySQL. > I run this type of query all the time on Firebird and Postgres now supports > the same CTE functions. > > -- > Lester Caine - G8HFL > ----------------------------- > > > I'm currently using MySQL but I'll switch databases if there's a compelling > reason and no drawbacks. > Thanks for the lead, I'm googling recursive queries. > > Cheers > Arno > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > I would take a long hard read of this article http://web.archive.org/web/20100105135622/http://dev.mysql.com/tech-resources/articles/hierarchical-data.html I can't find it anywhere else now - it used to be on the mysql site - but gone since Oracle has it and I can't find it in Google Cache. But, it explains the pros and cons of using the Adjacency List Model vs the Nested Set Model. The article is quite old (the copyright on the page is 2008, but I've no idea when it was actually created) and so, there are advances in SQL features (CTE's being one of them) which aren't mentioned. But, I've found Nested Sets to be much easier for me to work with, allowing me to provide quite complex searching based upon an n-level tree. How you visualise the data won't change. It is still, visually at least, a set of parent/child relationships, but to build a tree, you don't need to use recursion. In most cases, a single query will be enough to interact with the tree at any level, in any direction, for more or less any purpose. I recently bought "Joe Celkos SQL for Smarties: Advanced SQL Programming" on eBay. It covers a LOT more about Nested Sets. (http://desc.shop.ebay.co.uk/i.html?_nkw=Celkos+SQL+Smarties&_sacat=0&_dmpt=Non_Fiction&_odkw=Celkos+SQL+Smarties&_osacat=0&_trksid=p3286.c0.m270.l1313&LH_TitleDesc=1 currently showing 2 entries) and a fourth edition on Amazon (http://www.amazon.com/Joe-Celkos-SQL-Smarties-Fourth/dp/0123820227/ref=sr_1_1?ie=UTF8&qid=1311847652&sr=8-1) -- Richard Quadling Twitter : EE : Zend : PHPDoc @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY : bit.ly/lFnVea -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
