parent and a child level (because of the single JOIN). You can extend that
algorithm by adding additional JOIN clauses, but that's not a solution,
because it requires you to know how deep the maximum nesting is when you're
writing the code, and that's usually not possible, unless the application
has a hard limit on the number of levels (which typically is not the case).
barneyb
-----Original Message-----
From: Ross, Jason [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 9:43 AM
To: CF-Talk
Subject: FW: MySQL Heirarchies (fwd)
wouldn't this be pretty close?
SELECT parents.id AS parent, children.id as child
FROM parentChild parents
INNER JOIN parentChild children
ON children.par_id = parents.id;
Need to add something in there for the NULL par_id's, but
good enough to get the wheels turning maybe ...
mysql> select * from parentChild;
+----+--------+-----------+
| id | par_id | comment |
+----+--------+-----------+
| 1 | NULL | blah blah |
| 2 | 1 | blah blah |
| 3 | 2 | blah blah |
| 4 | 1 | blah blah |
| 5 | 4 | blah blah |
| 6 | 5 | blah blah |
| 7 | 6 | blah blah |
| 8 | NULL | blah blah |
| 9 | 8 | blah blah |
| 10 | 9 | blah blah |
+----+--------+-----------+
mysql> SELECT parents.id AS parent, children.id as child
-> FROM parentChild parents
-> INNER JOIN parentChild children
-> ON children.par_id = parents.id;
+--------+-------+
| parent | child |
+--------+-------+
| 1 | 2 |
| 2 | 3 |
| 1 | 4 |
| 4 | 5 |
| 5 | 6 |
| 6 | 7 |
| 8 | 9 |
| 9 | 10 |
+--------+-------+
8 rows in set (0.00 sec)
----- Original Message -----
> From: Haggerty, Mike
> To: CF-Talk
> Sent: Monday, October 20, 2003 2:58 PM
> Subject: RE: MySQL Heirarchies
>
>
> Well, the best solution at that point does not involve a database, but
> I appreciate your thoughts.
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]