Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

recursive query problem

 
vikas sharmaa
Ranch Hand
Posts: 191
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic