From: mcstockX@Xenquery   
      
   "sajid"  wrote in message   
   news:a2ab112e.0312072138.c9d8130@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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)