Oracle Query

[sajid]

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

[Mark C. Stock]

Re: Oracle Query


"sajid" <sajid321@yahoo .comwrote in message
news:a2ab112e.0 312072138.c9d81 30@posting.goog le.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

[sajid]

Re: Oracle Query

"Mark C. Stock" <mcstockX@Xenqu ery .comwrote in message news:<hNKdnQslq ot39Umi4p2dnA@c omcast.com>...

"sajid" <sajid321@yahoo .comwrote in message
news:a2ab112e.0 312072138.c9d81 30@posting.goog le.com...
| This is a treeview
|
| Root
| -- (Level 1) Cricket1
| ---(Level 2) Cricket2
| --- (Level 3) sachin
| --(Level1) Cricket3
| --(Level2)dravid
|
| --(Level1)FootBAL L1
| --(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..,tha nks 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

[VC]

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,'Roo t' ,'Root');
insert into t1 values(101,'cri cket' ,'Cricket1');
insert into t1 values(102,'cri cket' ,'Cricket2');
insert into t1 values(103,'pla yer' ,'sachin');
insert into t1 values(104,'cri cket' ,'Cricket3');
insert into t1 values(105,'pla yer' ,'dravid');
insert into t1 values(106,'foo tball','FootBAL L1');
insert into t1 values(107,'pla yer' , '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.parent id and (prior typ != 'cricket' or
typ!='player')
start with t2.parentid is null;

Root
-Cricket1
--Cricket2
-Cricket3
-FootBALL1
--pele

Rgds.


"sajid" <sajid321@yahoo .comwrote in message
news:a2ab112e.0 312090531.1cdc6 b29@posting.goo gle.com...

"Mark C. Stock" <mcstockX@Xenqu ery .comwrote in message

news:<hNKdnQslq ot39Umi4p2dnA@c omcast.com>...

"sajid" <sajid321@yahoo .comwrote in message
news:a2ab112e.0 312072138.c9d81 30@posting.goog le.com...
| This is a treeview
|
| Root
| -- (Level 1) Cricket1
| ---(Level 2) Cricket2
| --- (Level 3) sachin
| --(Level1) Cricket3
| --(Level2)dravid
|
| --(Level1)FootBAL L1
| --(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..,tha nks 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

[sajid]

Re: Oracle Query

Hello VC
thanks for the answer, "prior type" was thing i was missing

rgs

sajid