Re: Oracle Query

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" <sajid321_at_yahoo.com> wrote in message news:a2ab112e.0312090531.1cdc6b29_at_posting.google.com... > "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

> >

>
> 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