pmohanan
asked on
Child-Parent Relation SQL !!!!!!!!!!
Hi..All,
I have a table with data stored in a tree structure i,e parent-child relation structure.
For eg if the tree structure looks like this :
Parent : 0
Child : 01, 02, 03, 04
Now for parent : 01
Child : 011, 012
Parent : 02
Child : 021,022
Parent : 03
child : 031, 032
04 is the bottom of the its branch
Again for Parent : 031
childs : 0311, 0312
Now the query should return me these rows,
0,011
0,012
0,021
0,022
0,0311
0,0312
0,04
01,011
01,012
02,021
02,022
03,0311
03,0312
031,0311
03,0312
i.e at each level , i have to print the level nodes & the bottom most leaf of the branch. In this example
the bottom nodes are 011, 012, 021, 022, 0311, 0312, 032, 04
The levels are not fixed. I want a generic sql which will work for any tree structure wiht depth n.
I have a table with data stored in a tree structure i,e parent-child relation structure.
For eg if the tree structure looks like this :
Parent : 0
Child : 01, 02, 03, 04
Now for parent : 01
Child : 011, 012
Parent : 02
Child : 021,022
Parent : 03
child : 031, 032
04 is the bottom of the its branch
Again for Parent : 031
childs : 0311, 0312
Now the query should return me these rows,
0,011
0,012
0,021
0,022
0,0311
0,0312
0,04
01,011
01,012
02,021
02,022
03,0311
03,0312
031,0311
03,0312
i.e at each level , i have to print the level nodes & the bottom most leaf of the branch. In this example
the bottom nodes are 011, 012, 021, 022, 0311, 0312, 032, 04
The levels are not fixed. I want a generic sql which will work for any tree structure wiht depth n.
Your relationship structure should be maintained by a table with
parent child
so you would have
0 01
0 02
0 03
0 04
01 011
01 012
For this structure a query that will get the entire structure is (for sql server - can be adapted for others).
From an answer I have given previously. You can adapt this to give your solution.
General way to handle trees. Fails when the squence varchar length is exceeded - in this case 100 levels.
Problems for more than 800.
create table #z (id int, name varchar(20), threadID int null)
insert #z select 1, 'foo', null
insert #z select 2, 'foo2', null
insert #z select 3, 'foo2a', 2
insert #z select 4, 'foo2b', 2
insert #z select 5, 'foo3', null
insert #z select 6, 'foo3b', 5
insert #z select 7, 'foo3ba', 6
insert #z select 8, 'foo4', null
insert #z select 9, 'fooa', 1
insert #z select 10, 'foo2ba', 4
drop table #a
go
create table #a (id int, sequence varchar(1000), levelNo int)
insert #a select id, right(space(10) + convert(varchar(10),id),10 ), 1 from #z where threadid is null
declare @i int
select @i = 0
while @@rowcount > 0
begin
select @i = @i + 1
insert #a
select #z.id, sequence + right(space(10) + convert(varchar(10),#z.id) ,10), @i + 1
from #z, #a
where #a.levelNo = @i
and #z.threadID = #a.id
end
select #z.name
from #a, #z
where #a.id = #z.id
order by sequence
parent child
so you would have
0 01
0 02
0 03
0 04
01 011
01 012
For this structure a query that will get the entire structure is (for sql server - can be adapted for others).
From an answer I have given previously. You can adapt this to give your solution.
General way to handle trees. Fails when the squence varchar length is exceeded - in this case 100 levels.
Problems for more than 800.
create table #z (id int, name varchar(20), threadID int null)
insert #z select 1, 'foo', null
insert #z select 2, 'foo2', null
insert #z select 3, 'foo2a', 2
insert #z select 4, 'foo2b', 2
insert #z select 5, 'foo3', null
insert #z select 6, 'foo3b', 5
insert #z select 7, 'foo3ba', 6
insert #z select 8, 'foo4', null
insert #z select 9, 'fooa', 1
insert #z select 10, 'foo2ba', 4
drop table #a
go
create table #a (id int, sequence varchar(1000), levelNo int)
insert #a select id, right(space(10) + convert(varchar(10),id),10
declare @i int
select @i = 0
while @@rowcount > 0
begin
select @i = @i + 1
insert #a
select #z.id, sequence + right(space(10) + convert(varchar(10),#z.id)
from #z, #a
where #a.levelNo = @i
and #z.threadID = #a.id
end
select #z.name
from #a, #z
where #a.id = #z.id
order by sequence
ASKER
Hi..All,
I am sorry. I should have been more specific. I am using Oracle 8.1.5 . The table looks like this
Accounts
--------
parent Number(10),
child number(10),
description number (10)
Kangaroo, I was trying to search for INNER Join in oracel documentation. But I was not able to find it. Can you please let me know where can I find the documentation on INNSER JOIN.
I am sorry. I should have been more specific. I am using Oracle 8.1.5 . The table looks like this
Accounts
--------
parent Number(10),
child number(10),
description number (10)
Kangaroo, I was trying to search for INNER Join in oracel documentation. But I was not able to find it. Can you please let me know where can I find the documentation on INNSER JOIN.
Joins are used to combine table, but rewriting the snippet may be more illustrative:
SELECT node.*
FROM node INNER JOIN vertex ON node.nodeid = vertex.self
WHERE vertex.parent = <specific node>
Without using the JOIN specification this becomes:
SELECT node.*
FROM node, vertex
WHERE node.nodeid = vertex.self
vertex.parent = <specific node>
SELECT node.*
FROM node INNER JOIN vertex ON node.nodeid = vertex.self
WHERE vertex.parent = <specific node>
Without using the JOIN specification this becomes:
SELECT node.*
FROM node, vertex
WHERE node.nodeid = vertex.self
vertex.parent = <specific node>
Can't you use 'connect' in oracle to do this?
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
- PAQ'd and pts removed
Please leave any comments here within the
next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !
Nic;o)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There is a quite a lot you can do with SQL, but recursive data-structure can, as far as I know, not be implemented with it.
You can set up the data:
table node:
nodeid: int;
data : whatever-datatype;
table vertex
self : int -> nodeid
parent: int -> nodeid
and find all the children of a specific node
SELECT node.*
FROM node INNER JOIN vertex ON node.nodeid = vertex.self
WHERE vertex.parent = <specific node>