recursive query

[overDeveloper]

I need a query to produce a tree node - something like:

parent 1
--- child 1a
--- child 1b
--- child 1c
parent 2
--- child 2a
--- child/parent 2b
--- child 2b1
--- child 2b2

all of these are in the same table that looks something like:

id parentID hasChild name

I can change the fields if need be, but does anyone know how I can create a query that I would simply loop through to reproduce this?

 

[ptheriault]

Here is a link that will help you come up with a query.

http://www.dbazine.com/oracle/or-articles/tropashko4

Have you written anything yet? Do you have something for us to look at.



- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[overDeveloper]

No, I do not have anything done yet - I am just trying to figure out how much of this SQL should handle and how much .net should handle... Ideally, I'd like to accomplish it all in SQl and just loop through the results, but I am not sure if that is even possible....

 

[ptheriault]

Is this what you are looking for?

[COLOR=blue]DECLARE[/color] @tree [COLOR=blue]TABLE[/color] (id [COLOR=blue]int[/color] [COLOR=blue]identity[/color](1,1)
                     ,ParentID [COLOR=blue]int[/color]
                     ,hasChild [COLOR=blue]bit[/color]
                     ,descr [COLOR=blue]varchar[/color](100))

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @tree [COLOR=blue]VALUES[/color](0,1,[COLOR=red]'Dad'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @tree [COLOR=blue]VALUES[/color](0,1,[COLOR=red]'Dad1'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @tree [COLOR=blue]VALUES[/color](0,1,[COLOR=red]'Dad3'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @tree [COLOR=blue]VALUES[/color](1,0,[COLOR=red]'Child'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @tree [COLOR=blue]VALUES[/color](2,0,[COLOR=red]'Child2'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @tree [COLOR=blue]VALUES[/color](3,0,[COLOR=red]'Child3'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @tree [COLOR=blue]VALUES[/color](1,0,[COLOR=red]'Child4'[/color])

[COLOR=blue]select[/color] * [COLOR=blue]from[/color] @tree

[COLOR=blue]SELECT[/color] a.descr [COLOR=blue]as[/color] parent
       ,b.descr [COLOR=blue]as[/color] child
    [COLOR=blue]FROM[/color] @tree a
     [COLOR=blue]JOIN[/color] ([COLOR=blue]select[/color] id,ParentID,descr
            [COLOR=blue]from[/color] @tree) b
      [COLOR=blue]on[/color] b.Parentid = a.id

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ca8msm]

I'd have thought this was more suited to the front end doing the work.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.