Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

parent child relation ship

 
Sreenath Madasu
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Adam Hardy
Ranch Hand
Posts: 567
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Sreenath Madasu
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please if somebody can help!
This is an urgent problem
 
Michael Matola
whippersnapper
Ranch Hand
Posts: 1820
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the soln.
I will look into it.
 
John Ryan
Ranch Hand
Posts: 124
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have tried that(recursive) but could not get in the form wanted.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic