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" <sa******@yahoo.com> wrote in message
news:a2**************************@posting.google.c om...
"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message
news:<hN********************@comcast.com>...
"sajid" <sa******@yahoo.com> wrote in message
news:a2*************************@posting.google.co m...
| 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
