homepage Welcome to WebmasterWorld Guest from 54.161.246.212
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Hardware and OS Related Technologies / Website Technology Issues
Forum Library, Charter, Moderators: phranque

Website Technology Issues Forum

    
SQL Question
How would I write recursive sql?
jpmirick

10+ Year Member



 
Msg#: 348 posted 2:20 pm on Mar 26, 2001 (gmt 0)

I have a DB table (category) which contains three columns (name, id, and parentId). If I had the following three records.

bedroom,1,null
bed,2,1
sheet,3,2

I would like to write some sql that could print out the following.

bedroom
bedroom>bed
bedroom>bed>sheet

I have no idea how to do this... could someone lend a helping hand?

thanks... if this isnt the right forum could you point me in the right direction.

 

DaveAtIFG

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 348 posted 3:25 am on Mar 27, 2001 (gmt 0)

Welcome to WebmasterWorld jpmirick! Since no one jumped right in with an answer and I haven't a clue, I did a search for an SQL forum. Ironiclly, I found a thread at tek-tips with your question answered at [tek-tips.com...] but don't be a stranger! ;)

jpmirick

10+ Year Member



 
Msg#: 348 posted 4:01 am on Mar 27, 2001 (gmt 0)

Yeah I saw that too... dont think it really answered the question since the answer given really only accounts for something that would go three levels deep.

If I had the following records:

bedroom,1,null
bed,2,1
sheet,3,2
lint,4,3

it wouldnt work with the solution given.

I was really looking for a recursive solution.

hehe.. you might find the same question a few places, I have been searching all day for the answer (well actually I was hoping someone else would figure it out).
-jmirick

Xoc

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 348 posted 4:01 pm on Mar 27, 2001 (gmt 0)

This is a limitation of SQL. There is no way in standard SQL to do a complete parts breakdown that would recurse infinitely deep. There are some extensions to SQL that would do it, but it depends on your database engine on whether it supports those extensions.

You can write SQL that would work against a given depth, but not an arbitrary depth.

Relational purists don't like SQL for the reason that it can't do this sort of breakdown.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Hardware and OS Related Technologies / Website Technology Issues
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved