From: boston103@hotmail.com   
      
   Hello sajid,   
      
   Well,  it's easy enough.  In Oracle 9i:   
      
   drop table t1;   
   create table t1(Id int,  typ varchar2(10),  name varchar2(10));   
   insert into t1 values(100,'Root'    ,'Root');   
   insert into t1 values(101,'cricket' ,'Cricket1');   
   insert into t1 values(102,'cricket' ,'Cricket2');   
   insert into t1 values(103,'player'  ,'sachin');   
   insert into t1 values(104,'cricket' ,'Cricket3');   
   insert into t1 values(105,'player'  ,'dravid');   
   insert into t1 values(106,'football','FootBALL1');   
   insert into t1 values(107,'player' , 'pele');   
      
      
   drop table t1;   
   create table t2 (id int, ParentId int);   
   insert into t2 values(100,null);   
   insert into t2 values(101,100);   
   insert into t2 values(102,101);   
   insert into t2 values(103,102);   
   insert into t2 values(104,100);   
   insert into t2 values(105,104);   
   insert into t2 values(106,100);   
   insert into t2 values(107,106);   
      
   select lpad('-', level-1, '-')||name name from t1 join t2 on t1.id=t2.id   
     connect by prior t2.id=t2.parentid and (prior typ != 'cricket' or   
   typ!='player')   
       start with t2.parentid is null;   
      
   Root   
   -Cricket1   
   --Cricket2   
   -Cricket3   
   -FootBALL1   
   --pele   
      
   Rgds.   
      
      
   "sajid"  wrote in message   
   news:a2ab112e.0312090531.1cdc6b29@posting.google.com...   
   > "Mark C. Stock"  wrote in message   
   news:...   
   > > "sajid"  wrote in message   
   > > news:a2ab112e.0312072138.c9d8130@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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)