List: General Discussion « Previous MessageNext Message » From: Peter Brawley Date: September 24 2007 10:15pm Subject: Re: recursion or something recursion-esque View as plain text
```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!
>
>
>
```