SQL Question

How would I write recursive sql?

[jpmirick]

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]

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]

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]

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.