This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes query nested data ( interview question) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "query nested data ( interview question)" Watch "query nested data ( interview question)" New topic
Author

query nested data ( interview question)

vu lee
Ranch Hand

Joined: Apr 19, 2005
Posts: 189
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
.......
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

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.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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

Joined: Apr 19, 2005
Posts: 189
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.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

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 Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

my sql didn't support this last time i tried, it had to be done programatically
 
wood burning stoves
 
subject: query nested data ( interview question)
 
Similar Threads
How Do I Insert .... ?
Implicit objects in JSP
package and class question
Preaching to the Converted
JSP where to start?