468,101 Members | 1,338 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,101 developers. It's quick & easy.

Oracle Query

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
Jul 19 '05 #1
4 8642

"sajid" <sa******@yahoo.com> wrote in message
news:a2*************************@posting.google.co m...
| 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
Jul 19 '05 #2
"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
Jul 19 '05 #3
VC
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

Jul 19 '05 #4
Hello VC
thanks for the answer, "prior type" was thing i was missing

rgs

sajid
Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by francis70 | last post: by
4 posts views Thread by susmita_ganguly | last post: by
11 posts views Thread by Markus Breuer | last post: by
3 posts views Thread by Jon Ole Hedne | last post: by
8 posts views Thread by Alfonso Esteban Gonzalez Sencion | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.