Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Self Join Query Help

 
Srikkanth Mohanasundaram
Ranch Hand
Posts: 243
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
 
Srikkanth Mohanasundaram
Ranch Hand
Posts: 243
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author
Saloon Keeper
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can use a fixed depth but arbitrary length requires greater flexibility than SQL allows, such as stored procedures.
 
Srikkanth Mohanasundaram
Ranch Hand
Posts: 243
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Jeanne.Thanks Scott.

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

Srikkanth
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic