Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!

# recursive query problem

vikas sharmaa
Ranch Hand
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
Posts: 1082
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

Scott Selikoff
author
Saloon Keeper
Posts: 4014
18
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 ]