Re: Oracle Query

"sajid" <sajid321_at_yahoo.com> wrote in message news:a2ab112e.0312072138.c9d8130_at_posting.google.com...
| This is a treeview
|
| Root
| -- Cricket1
| ---Cricket2
| ---sachin
| --Cricket3
| --dravid
|
| --FootBALL1
| --pele
|
|
|
|
| I have a table like this
|
| Id type name
| 100 Root Root
| 101 cricket Cricket1
| 102 cricket Cricket2
| 103 player sachin
| 104 cricket Cricket3
| 105 player dravid
| 106 football FootBALL1
| 107 player pele
|
|
|
| To maintain the parent-child relationship i have the following
| relation table
|
|
| id ParentId
|
| 100 null
| 101 100
| 102 101
| 103 102
| 104 100
| 105 104
| 106 100
| 107 106
|
|
|
| I need to query the db and the get the following result
|
|
|
| Root
| -- Cricket1
| ---Cricket2
|
| --Cricket3
|
|
| --FootBALL1
| --pele
|
|
|
| means when ever it encounters the type of cricket it should not get
| the childs inside it(as in Cricket3) however if it encounters a child
| of type cricket , it should go ahead and get the child (as in
| Cricket1)
|
| Its oracle db , so start with connect by clause can be used
|
| Thanks in Advance
|
| sajid

it looks like you have a simple 1:M hierarchy -- that should be modelled in a single table, not two

you are correct that CONNECT BY can be used -- but you seem to imply that you are having trouble with it and would like someone to show you how to do it with your data.

i would suggest you try the examples in the Oracle SQL Manual (under SELECT) until you understand how it works, then apply the technique to your own data. if you have problems after to try it yourself, go ahead and post your statement and errors (and db version) and no doubt you'll get plenty of help

• mcs