standard for recursive joins called a "with statement." It's supposed to do
the same sort of thing (though it looked more complicated) as Oracle's
connect by...prior clause.
Link:
http://archives.postgresql.org/pgsql-general/2000-06/msg00449.php
----- Original Message -----
From: "Haggerty, Mike" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, October 21, 2003 4:04 PM
Subject: RE: MySQL Heirarchies (fwd)
> Yeah, ideally the solution would be one that allows the query (or proc,
> 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]
