From: Peter Brawley
Date: September 24 2007 10:15pm
Subject: Re: recursion or something recursion-esque
List-Archive: http://lists.mysql.com/mysql/209237
Message-Id: <46F836ED.9010800@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Mike,

 >What I'd love to do is pull all children (and grandchildren, etc) per
 >each, such that I'd end up with the following result set or something

See http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html 
for theory & examples.

PB

Mike Johnson wrote:
> This one may end up dead in the water, but I figured I'd run it past the
> group as I've seen some pretty creative solutions in my time here.
>
> Let's say I have a table like this:
>
> +----+-----------+
> | id | parent_id |
> +----+-----------+
> |  1 |         0 |
> |  2 |         0 |
> |  3 |         2 |
> |  4 |         0 |
> |  5 |         1 |
> |  6 |         2 |
> |  7 |         1 |
> |  8 |         3 |
> |  9 |         8 |
> | 10 |         5 |
> +----+-----------+
>
> id is the primary key and parent_id refers to this table's id. That is,
> 3 is a child of 2 and 8 is a child of 3.
>
> What I'd love to do is pull all children (and grandchildren, etc) per
> each, such that I'd end up with the following result set or something
> like it:
>
> +----+------------+
> | id | children   |
> +----+------------+
> |  1 | 5, 7       |
> |  2 | 3, 6, 8, 9 |
> |  3 | 8, 9       |
> |  4 |            |
> |  5 | 10         |
> |  6 |            |
> |  7 |            |
> |  8 | 9          |
> |  9 |            |
> | 10 |            |
> +----+------------+
>
> Say there's more to this table than what you see, and say it's a lookup
> table to a larger table. If I'm querying on everything in that larger
> table that's 2 here, I'd like it to be able to actually pull anything
> that's 2, 3, 6, 8, or 9. The obvious solution is to parse out an array
> of that ahead of time and use it (1 => (5, 7), 2 => (3, 6, 8, 9), etc),
> but let's pretend this is an annoyingly complex Perl suite and if I can
> just manipulate queries I'll be a whole lot happier.
>
> Any thoughts? I feel like the solution is either remarkably simple or
> frustratingly difficult. Thanks in advance if you can help!
>
>
>