[thelist] one for the database gurus - recursive query

[Bruce Heerssen]

Heya 'volters,

I'm developing a category system that uses a parent-child relationship to create
a flexible hierarchical data structure. IE:

cat_id	PK numeric
parent_id	numeric (0 for top-level cats, cat_id for children)
cat_desc	varchar2

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.

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)


A boatload of thanks and a beer to anyone who gets it right. And I'm serious
about the beer :)

- Bruce