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

recursive query problem

vikas sharmaa
Ranch Hand

Joined: Jun 28, 2007
Posts: 191
Emp table has 3 columns: emp_id, mgr_id, and level.

this table creates hierarchical structure with level column store the level of tree. emp_id and mgr_id has many to one relationship (1 manager could have multiple emp, but every emp has at most 1 manager).

now, i would like to write a query that will fetch all the managers and their managers' managers and so on upto the top of hierarchy of the given emp_id. what shall be the query for that?

for example, our table has below data:

emp_id mgr_id level
10 50 1
50 60 2
60 80 3
20 60 1
80 90 1

then for emp_id 80, it will fetch 60, 50, and 10 values.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Hi Vikas,

I am not sure if I understood your problem correct ! Please correct me if I am wrong....

Originally posted by vikas sharmaa:
but every emp has at most 1 manager




I think It can not be possible. Just take the example of your table's data only.

The table has below data:

emp_id mgr_id level
10 50 1
50 60 2
60 80 3
20 60 1
80 90 1


Assuming that table has only 5 rows, then who is Manager of mgr_id 90, even mgr_id 90 is no where present as emp_id. So somewhere emp_id should have mgr_id as null.

Originally posted by vikas sharmaa:

now, i would like to write a query that will fetch all the managers and their managers' managers and so on upto the top of hierarchy of the given emp_id.

then for emp_id 80, it will fetch 60, 50, and 10 values




I again think here your data is wrong, If you given emp_id as 80, it has mgr_id as 90 and mgr_id 90 has no manager.


Thanks,
Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

You can't write an undefined-depth recursive query without using a stored procedure. Basic SQL syntax does not allow for it.

Keep in mind stored procedures are the bane of many developers existence (since they are hard to maintain) so there are some other options. One is to maintain a de-normalized table (maintained by triggers or materialized view) that maps all parent-to-child relationships for every manager. It makes it fast on querying but can grow large in space and slow if proper indexes aren't used. You could also query in JDBC although performance would likely be a factor. I find a materialized view to be the best all around solution although configuring one properly is non-trivial.
[ August 19, 2008: Message edited by: Scott Selikoff ]

My Blog: Down Home Country Coding with Scott Selikoff
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: recursive query problem