From: Peter Brawley
Date: January 17 2012 1:31am
Subject: Re: Parent/Child - Linked List
List-Archive: http://lists.mysql.com/mysql/226617
Message-Id: <4F14CF79.7040904@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 1/16/2012 4:14 PM, bruce wrote:
> 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.

Mistake. use Null.
>
> The items are (0,1), and (0,8).

Then the table has two trees. See "Multiple trees in one table" at 
http://www.artfulsoftware.com/infotree/queries.php.

PB

-----
>
> 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.brawley@stripped>  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.brawley@stripped>  wrote:
>>>> On 1/16/2012 12:53 PM, bruce wrote:
>>>>> On Mon, Jan 16, 2012 at 1:52 PM, bruce<badouglas@stripped>      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.artful@stripped>
>>>>>>   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
>>>>>>>
>>>>>>>