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?
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 ]