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!
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]