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

--
Mike Johnson                 Smarter Travel Media LLC
Applications Developer       http://www.smartertravel.com/
```