function, etc.) to sort the results according to the parent-child
relationship at a variable number of levels.
In SQL Server and Oracle, I can create a temporary table and loop around
it for each row in the recordset. I assign a recordnumber and
levelnumber to each row: the recordnumber is the vertical position of
the record, and the levelnumber is the depth of the row in the results
tree (like a child-of-child-of-child-of-parent relationship, levelnumber
is a 3).
For right now I am doing the sorting in CF. Sure would be nice if MySQL
supported this kind of procedure, or if there was a join in any database
that supported recursive joins in queries. Someone told me DB2 briefly
had or was considering something called an ABOVE JOIN in the early 90's
that did exactly this, where you could link a column in a table to
itself for purposes of sorting heirarchical information.
M
-----Original Message-----
From: Barney Boisvert [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 12:55 PM
To: CF-Talk
Subject: RE: MySQL Heirarchies (fwd)
It needs to go an arbitrary number of levels, right now it's
limited to a
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]
