Win a copy of Spring Boot in Practice this week in the Spring forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Liutauras Vilda
  • Henry Wong
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Al Hobbs
  • Carey Brown
Bartenders:
  • Piet Souris
  • Mikalai Zaikin
  • Himai Minh

Self Join Query Help

 
Ranch Hand
Posts: 243
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
author & internet detective
Posts: 41185
848
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
See this recursive query thread.
 
Srikkanth Mohanasundaram
Ranch Hand
Posts: 243
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 41185
848
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
author
Posts: 4323
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Jeanne.Thanks Scott.

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

Srikkanth
reply
    Bookmark Topic Watch Topic
  • New Topic