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

Self Join Query Help

Srikkanth Mohanasundaram
Ranch Hand

Joined: Feb 07, 2007
Posts: 185
Hello all,

I have a table like the one shown below
id parentChannelId
------------------------
item1 null
item2 item1
item3 item1
item4 item2
item5 item3
item6 item6
.....so on
..so on
I would want to get all the children (which includes the grandchildren) of item1 , I know i need to do a self join.But i am not sure on how to achieve this


This would return item2,item3 How can i proceed further?

Thanks,
Srikkanth
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30309
    
150

See this recursive query thread.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Srikkanth Mohanasundaram
Ranch Hand

Joined: Feb 07, 2007
Posts: 185
Thanks for the link Jeanne.

I read the thread and it means I should know the depth (number of levels). So assuming I've 3 levels now, i create a view with the parent-child mapping. In future if i need to increase the depth , i'll have to go back and update the view with one more join right ? Please correct me if am wrong.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30309
    
150

Srikkanth Mohanasundaram wrote:I read the thread and it means I should know the depth (number of levels).

Or use a stored procedure.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

You can use a fixed depth but arbitrary length requires greater flexibility than SQL allows, such as stored procedures.


My Blog: Down Home Country Coding with Scott Selikoff
Srikkanth Mohanasundaram
Ranch Hand

Joined: Feb 07, 2007
Posts: 185
Thanks Jeanne.Thanks Scott.

I think i would use a fixed length for my case.

Srikkanth
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Self Join Query Help