Re: Oracle Query

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:<hNKdnQslqot39Umi4p2dnA_at_comcast.com>...
> "sajid" <sajid321_at_yahoo.com> wrote in message
> news:a2ab112e.0312072138.c9d8130_at_posting.google.com...
> | This is a treeview
> |
> | Root
> | -- (Level 1) Cricket1
> | ---(Level 2) Cricket2
> | --- (Level 3) sachin
> | --(Level1) Cricket3
> | --(Level2)dravid
> |
> | --(Level1)FootBALL1
> | --(Level2)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

Dear mark
Great that u noticed it is simple 1:m hierarchy..,thanks for the advice to model in a single table,but dear dude dont look at the table structure, that was just a example i gave to make it simple , u should have read the explanation properly

my question was using start with connect by clause 1.u can easily traverse thro all the childs for a particular Id 2.u can even stop at some particular type -- say stop at cricket/football ... ok
3.my question is,it stops at the first occurence of cricket and if the immediate child happens to be cricket again (then i need to display that child cricket also else stop) .. In my case i am not able to display cricket2 under cricket1

For the clarity purpose i have put the levels besides the hierarchy

ok bye
sajid Received on Tue Dec 09 2003 - 14:31:54 CET