On 28 July 2011 14:09, Arno Kuhl <arno@xxxxxxxxxxxxxx> wrote: >> >> 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? > > You say you have a parentid and an id - ie, two specific fields in your > records. You say that you want to query all the parentids. Nobody else has > said this, but why not just > > Select unique ids where parentid=0 ? That gives you (as you say) all your > top-level records, which are the parents of everything, no? Or are you > looking for each id that is itself a parent to something else? If the > latter, then why not > select unique parentid where parentid <> 0 ? > > -- > > Hi Jim. I wanted the list of related parentids from current id to top-level > (parentid=0). > That could be 0 or more results, regardless of how many non-zero parentids > there are in total. > Currently I get this from selecting id=parentid while parentid>0, ie > recursively select each record going up the tree to the top level. > The nested set model apparently can return the entire hierarchical list from > the current node to the top level with a single select, but I haven't run > any tests yet on my specific data. > > Cheers > Arno > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > If your data is based upon parentid and uniqueid, then you will need to change your data... http://data.bangtech.com/sql/nested_set_treeview.htm and once you've done that and find you can't deal with things (maybe it isn't for you), then http://pratchev.blogspot.com/2007/02/convert-tree-structure-from-nested-set.html will help you turn it all back again. I've not used either of those but on the surface they seem adequate. But just look at the difference in the volume of code. NS => AL ... 1 query. Any depth. It shows just how simple using a NS is. I'll stop evangelising now. I REALLY like NS. -- 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
