[thelist] one for the database gurus - recursive query
rudy
r937 at interlog.com
Sat Feb 24 00:00:19 CST 2001
> Can someone please supply a query that can return the whole
> shebang grouped according to cat_id, cat_desc? I need one that
> will work in either ms access or ms sql server. I remember seeing
> rudy's query for oracle a few weeks back, but I think that one uses
> some proprietary SQL.
hi bruce
not sure i remember it... are you sure that was me? ;o)
(if so, could you please shoot me the name of the thread so i can go look
up what i said in the archives -- i usually file sql stuff in local folders
but i've just skimmed back two months and couldn't see anything about
recursion)
>To clarify, the recordset returned should look something like this:
>
>cat_id parent_id cat_desc
> 1 0 this is a top level category
> 473 1 this is a subcategory
> 574 473 this is a second subcategory
> 575 473 this is a second subcategory
> 2 0 another top level
> 236 2 child of cat_id(2)
that's pretty easy
i'm going to make a guess (from your use of the word "recordset") that you
have an ASP page
i seem to recall somebody (anthony?) posted a recursive way you can do this
in ASP
in any case traversing a tree is not child's play, as i'm sure you can see
from the ratface article -- i've also got some urls to articles by joe
celko that are equally disheartening (no offence, kirby)
if you can't find a recursive way to do it (no, i don't have one, not for
the generic case of N levels), then you might want to ask how many levels
deep this can honestly be expected to go, and if you can nail it down to a
fixed number, i can help you write an outer join that will quite happily do
what you want
rudy
More information about the thelist
mailing list