aspose file tools*
The moose likes JDBC and the fly likes parent child relation ship Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "parent child relation ship" Watch "parent child relation ship" New topic
Author

parent child relation ship

Sreenath Madasu
Ranch Hand

Joined: Mar 26, 2002
Posts: 32
Hello Folks!
Here is my problem
I have a table in the database which is self related. i.e. it has id and a parent id which an id itself.
example
id parent_id
1
2 1
3 2
4 2
5 2
6 1
8 1
9 2
10 3
11 3
I want to represent this in the form
1 2 3 10
1 2 3 11
1 2 4
1 2 5
1 2 9
1 6
in the java objects. Probably a vector of vectors or something like that.
So anybody has ideas please help me
Thanks


SCJP1.2<br />SCWCD1.4<br />SCBCD1.3
Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 566
Hi Sreenath,
what exactly is the problem? Are you getting your data out of the DB with a query OK? If not, try writing a query with a join from the table onto itself with a table alias.
How big is the resultset going to be? If it's not huge, then just duplicate the data, i.e. in your example you would get "1" occuring 8 times or so, just the same number of times you are displaying it. Personally I would make an array of beans, where each bean is my own bespoke bean for that object.
Adam


I have seen things you people would not believe, attack ships on fire off the shoulder of Orion, c-beams sparkling in the dark near the Tennhauser Gate. All these moments will be lost in time, like tears in the rain.
Sreenath Madasu
Ranch Hand

Joined: Mar 26, 2002
Posts: 32
Hi Adam!
The problem is like this. I will send the top most element i.e. for example 1, in this case to the program. It connects to the Db and finds out its root elements, their root elements, their root elements etc. until it comes to last element which has no children. For you benefit I show the data one again.
id parentid
1
2 1
3 2
4 2
5 2
6 1
8 1
9 2
10 3
11 3
Now 1 is sent as an input. then its roots are found out as 2,6 and 8. Again roots of 2 are found as 3,4,5 and 9. Roots of 3 are found as 10 and 11. ANd there is no further going down.
Hence the first row looks as
1 2 3 10
second row as
1 2 3 11
The total output looks as
1 2 3 10
1 2 3 11
1 2 4
1 2 5
1 2 9
1 6
1 8
Well thats the problem!
So please help me.
Thanks
Sreenath
Sreenath Madasu
Ranch Hand

Joined: Mar 26, 2002
Posts: 32
I forgot to mention that the data given is not the real data but sample. Real data might be in thousands.
Sreenath
Sreenath Madasu
Ranch Hand

Joined: Mar 26, 2002
Posts: 32
Please if somebody can help!
This is an urgent problem
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1746
    
    2
I think this is about as close as I can get you:
SELECT t3.parentId AS topmost,
t2.parentId AS intermediate1,
t1.parentId AS intermediate2,
t1.id as bottommost
FROM (table1 AS t1 LEFT JOIN table1 AS t2 ON t1.parentId = t2.id)
LEFT JOIN table1 AS t3 ON t2.parentId = t3.id
ORDER BY t1.id DESC;
--------------------------------------------------------
| topmost | intermediate1 | intermediate2 | bottommost |
--------------------------------------------------------
|       1 |             2 |             3 |         11 |
--------------------------------------------------------
|       1 |             2 |             3 |         10 |
--------------------------------------------------------
|         |             1 |             2 |          9 |
--------------------------------------------------------
|         |               |             1 |          8 |
--------------------------------------------------------
|         |               |             1 |          6 |
--------------------------------------------------------
|         |             1 |             2 |          5 |
--------------------------------------------------------
|         |             1 |             2 |          4 |
--------------------------------------------------------
|         |             1 |             2 |          3 |
--------------------------------------------------------
|         |               |             1 |          2 |
--------------------------------------------------------
|         |               |               |          1 |
--------------------------------------------------------

Trying to do this with right joins made my head hurt, so I wrote it with left joins. Actually I wrote this starting the from the bottom with the nested join, then I just switched the field order to make it more closely match the way you wanted the output.
As you can anticipate, having to code a join for every level will quickly get out hand if your tree is more than a few levels deep.
Maybe try a web search on representing trees in relational tables or traversing by query. There's probably some easier way of doing this, but I'm just not seeing it.
[ June 03, 2002: Message edited by: Michael Matola ]
Sreenath Madasu
Ranch Hand

Joined: Mar 26, 2002
Posts: 32
Thanks for the soln.
I will look into it.
John Ryan
Ranch Hand

Joined: Mar 14, 2001
Posts: 124
Why not write an recursive method where you give it the rootId/elementId (i.e 1) and some sort of collection class to hold its children elements. Within the recursive method execute a simple select to get an elemtents children. This will mean executing a query many times but at least it saves you trying to come up with a complex join.................
[ June 04, 2002: Message edited by: John Ryan ]
Sreenath Madasu
Ranch Hand

Joined: Mar 26, 2002
Posts: 32
I have tried that(recursive) but could not get in the form wanted.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: parent child relation ship