Link to home
Start Free TrialLog in
Avatar of pmohanan
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.

Avatar of KangaRoo
KangaRoo

>> I want a generic sql
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>
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

Avatar of pmohanan

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.
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>
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
Avatar of Jgould
Jgould

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial