List:General Discussion« Previous MessageNext Message »
From:AJBE (E-mail) Date:September 9 1999 1:28pm
Subject:Re: A Recursive Subquery is...
View as plain text  
A recursive subquery uses one table mutliple times as different aliases in
order to join to itself. The most common use is for a multi-level
heirarchial tree, as in a category tree. For example, the table itself must
have at least three fields:

id
parent_id
name

The root category records would look like this:

1, 0, "Category A"
2, 0, "Category B"
3, 0, "Category C"

Sub records of category A would be added to the same table like this:

4, 1, "Category A1"
5, 1, "Category A2"
6, 2, "Category B1" (sub of B)
7, 3, "Category C1" (sub of C)
8, 4, "Category A1a" (sub of A1)

... and so on. The benefits are:

One table to deal with.
Almost "Unlimited" branches. # of branches equals number of alisases in SQL.
Totally "portable" categories... you can just change the parent id.

I've done it before in Oracle, and it seems to be one of the only things
(besides SPs) that I miss.

Andy

John Millaway <JohnM@stripped> wrote in message
news:4152399B02BFD21183D5006097780DCB1BE8AB@EXCHANGE...
> Just curious, what's a recursive subquery? (As opposed to a normal
> subquery.)
>
>
> > Hello,
> >
> > I'm trying to create a category tree using one table. This
> > table will have
> > an auto-increment ID field, and a parent_ID field which will
> > refer to an ID
> > field.
> >
> > Through a SQL statement that will create different aliases
> > for the same
> > table, I should be able to create a hierarchial view of the
> > relationships.
> > This works on Oracle.
> >
> > I'm not sure, but I remember seeing that MySQL does not
> > support recursive
> > subqueries. If this is true, does anyone have an indication
> > as to when it
> > might? I cannot find any mention within the documentation or FAQ.
> >
> > Thanks,
> > Andy
> >
> > ========================
> >  Andy Brown
> >  brown.andy@stripped
> > ========================
> >
> >
> >
> >
> > ________________________________________________________________
> > Get FREE voicemail, fax and email at http://voicemail.excite.com
> > Talk online at http://voicechat.excite.com
> >
> > ---------------------------------------------------------------------
> > Please check
> > "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> > posting. To request this thread, e-mail
> > mysql-thread12408@stripped
> >
> > To unsubscribe, send a message to the address shown in the
> > List-Unsubscribe header of this message. If you cannot see it,
> > e-mail mysql-unsubscribe@stripped instead.
> >
>
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread12417@stripped
>
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail mysql-unsubscribe@stripped instead.
>


===============================
 Andy Brown
 eMail: brown.andy@stripped
 Phone: 401-726-1076
 Fax:   847-556-0162
===============================

Thread
Re: A Recursive Subquery is...E-mail)9 Sep
  • Re: A Recursive Subquery is...Thimble Smith9 Sep
    • Re: A Recursive Subquery is...felix k sheng9 Sep
      • Re: A Recursive Subquery is...Thimble Smith10 Sep
        • Re: A Recursive Subquery is...Michael Widenius14 Sep
          • Site ????Jagadish R.R.( ASI 99)14 Sep
            • Re: Site ????Bill Gerrard14 Sep
            • Re: Site ????Christian Mack14 Sep