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!
>
>
>
