• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

query nested data ( interview question)

 
Ranch Hand
Posts: 208
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Given a table with two columns:
Thread_id, thread_subId
Where each thread_subid could be a thread_id of another row. How do i create a sql to select a thread_is and all of its thread_subid nesting at different levels?

For instance:
1
1.1
1.1.1
1.1.2
1.2
......
1.3
.......
 
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
it depends on which database you are using
if you are using MySQL lots of selects, with the where clause changing every time.
If you are using oracle you can use START WITH and CONNECT BY

if you are using another database you want to use the word recursive in your google search.
 
Marshal
Posts: 28271
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Wendy Gibbons wrote:if you are using another database you want to use the word recursive in your google search.



Or the phrase "bill of materials" which seems to be used frequently in the examples you see for this feature.
 
vu lee
Ranch Hand
Posts: 208
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
using mysql db. This was one of the interview question (just edit the subject). what would be a better response? I tried self join but it didnt work.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

vu lee wrote:I tried self join but it didnt work.


Unless there is a support in MySQL for hierarchical queries similar to the one in Oracle (and I doubt there is), it generally cannot be done. It theoretically might be done in pure SQL if the maximum depth (number of levels) is known beforehand (before constructing the query). The reason is you need one self-join (parent-child) for every level and if you don't know how many levels there can be, you obviously cannot build that query. However, I don't know whether such query would be practical (ie. efficient) in MySQL - or in any other database.
 
Wendy L Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
my sql didn't support this last time i tried, it had to be done programatically
 
reply
    Bookmark Topic Watch Topic
  • New Topic