hi Peter. Sorry.. Been looking at this for awhile.
In the sample data/tbl I provided, it has two top level root/parents. Ie, I have two entries that don't have a parentID. I use 0 to be null. The items are (0,1), and (0,8). The (0,1) item, has a number of descendants. The (0,8) only has a single descendant. For my app, I'm going to have a number of top level items, and they're each going to have a number of descendants, where I don't know the number of descendant rows, or the number of actual descendants. But either way, once I get the descendant list, I still need some way of linking the childID of the descendant to the linked ID of the statusTBL so I can get the status of the childID/app. And like I said, I'm not quite sure how to proceed in an efficient manner on this. Thanks On Mon, Jan 16, 2012 at 4:08 PM, Peter Brawley <peter.braw...@earthlink.net> wrote: > On 1/16/2012 2:08 PM, bruce wrote: >> >> Hi Peter. >> >> Not a mysql guru... so I've never used stored procedures/sub-queries.. >> >> But it sort of makes sense. >> >> What I'm really trying to get is to be able to take a test table like >> below >> >> LOCK TABLES `parentChildTBL` WRITE; >> /*!40000 ALTER TABLE `parentChildTBL` DISABLE KEYS */; >> INSERT INTO `parentChildTBL` VALUES >> (0,1,1), >> (1,2,2), >> (1,3,3), >> (1,4,4), >> (2,5,5), >> (2,6,6), >> (2,7,7), >> (0,8,8), >> (8,9,9); >> UNLOCK TABLES; >> >> and to be able to generate the child/descendant list of the top two/2 >> items (1,8) > > > I don't understand "top two(1,8)". In general a non-procedural query of n > recursion levels requires n-1 joins. If the number of recursive references > is unknown beforehand, the only way to query the tree is via a stored > procedure. > > PB > > > ----- if I only have a single top level item.. and can do a left join.. but > I'm not sure how to accomplish this with two top items, unless I take a look > at the approach you provided. I'm looking at being able to compare a > 'status' from a linked tbl, that links on the childID... thanks On Mon, Jan > 16, 2012 at 2:33 PM, Peter Brawley <peter.braw...@earthlink.net> wrote: >>> >>> On 1/16/2012 12:53 PM, bruce wrote: >>>> >>>> On Mon, Jan 16, 2012 at 1:52 PM, bruce<badoug...@gmail.com> wrote: >>>>> >>>>> Hey Authur. >>>>> >>>>> Should have been more clear. I've looked over a number of sites. And >>>>> with the exception of the the articles that talk about using the >>>>> "Nested List" approach, nowhere did I find data on how to get a >>>>> complete list of the child descendants of a given 'root'/top item from >>>>> the parent/child TBL. >>> >>> >>> Look again, eg listings 7 through 7d in >>> http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html. >>> >>> PB >>> >>> ----- >>> >>> >>>>> Chunks of code/pointers would be seriously useful. >>>>> >>>>> Thanks >>>>> >>>>> >>>>> On Mon, Jan 16, 2012 at 12:18 PM, Arthur >>>>> Fuller<fuller.art...@gmail.com> >>>>> wrote: >>>>>> >>>>>> See the piece on trees at www.artfulsoftware.com. It goes into several >>>>>> variations of how to handle hierarchies. >>>>>> >>>>>> HTH, >>>>>> -- >>>>>> Arthur >>>>>> Cell: 647.710.1314 >>>>>> >>>>>> Prediction is difficult, especially of the future. >>>>>> -- Neils Bohr >>>>>> >>>>>> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
