recursive sql help

[n8shaw]

I have a table called CATEGORIES.

Cat_ID
Cat_Parent_ID
Cat_Desc
Cat_Level

Most of the categories in the table are nested 2 deep. However there are 4 of them that are nested 3 deep.

I need to pull out the data so I can nest it like so (using Cold Fusion on the server-side):

category
products
sub_category
products
fine_category
products

I am currently using this SQL statement, but it is not returning the full hierarchy that I need.

SELECT b.cat_desc as category, c.cat_desc as sub_category ,d.cat_desc as fine_category, d.cat_id
FROM Categories d LEFT OUTER JOIN Categories c ON d.CAT_PARENT_ID = c.CAT_ID
LEFT OUTER JOIN Categories b ON c.CAT_PARENT_ID = b.CAT_ID
ORDER by category, sub_category, fine_category

Help!!

 

[tdatgod]

Hi,
I would suggest looking at

thread220-39000

Which asked the same basic question in terms of traversing a tree structure within a single table.

My final post on that thread pointed out this is ANSI SQL forum and I asked if anyone knew an official ANSI SQL way to solve problem and no one responded therefore I assume there is no ANSI SQL way to solve this problem without knowing how many levels deep your tree is.

Now if you are using a particular vendor implementation of SQL, I suggest you ask your question in the forum dedicated to that database vendor.

A lot of them have Extended SQL syntax or Stored procedures which allow you to accomplish this.


Computer Associates: Datacom
Computer Associates: IDMS
Computer Associates: Jasmine
Computer Associates: Openingres
Corel: Paradox
Microsoft: SQL Server
IBM: DB2
Microsoft: FoxPro (versions 1 through 2.6)
Informix: Informix Dynamic Server
Microsoft: Access - General discussion
Lotus/IBM: Approach
Microsoft: Visual FoxPro (versions 3.0 and higher)
Oracle: Oracle release 5, 6, and 7
Oracle: Oracle release 8 and 8i
Sybase: Adaptive Server
Sybase: SQL Server
TopSpeed: Clarion
Computer Associates: Clipper
Inprise (Borland): Visual dBase
Pick Systems: Pick
Inprise (Borland): dBase
Progress Software: PROGRESS
FileMaker Inc.: FileMaker Pro
Unisys DBMS solutions
Pervasive: Pervasive.SQL
Pervasive: Btrieve
NCR: Teradata
Sapphire: DataEase
T.C.X DataKonsult AB: MySQL
-General database discussion
ODBC
Centura Software / Gupta : SQLBase



or maybe even the Cold fusion Forum.

Macromedia (Allaire): ColdFusion




hope that helps...