[thelist] one for the database gurus - recursive query

[rudy]

> 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