Welcome to WebmasterWorld Guest from 23.22.46.195

Forum Moderators: phranque

SQL Question

How would I write recursive sql?

   
2:20 pm on Mar 26, 2001 (gmt 0)

10+ Year Member



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.

3:25 am on Mar 27, 2001 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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! ;)
4:01 am on Mar 27, 2001 (gmt 0)

10+ Year Member



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

4:01 pm on Mar 27, 2001 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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.

 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month