SQL help please, it shouldn't be this hard.

I'm trying to write a query that for some reason seems harder than it should be.

I have a large group of managers in a hierchy. Each employee is stored in an EMP table, with a foreign key to their manager.
The data is stored in a separate table, and is only related to non-supervisors.
Many managers supervise several levels (i.e. they have both supervisors, and non-supervisors reporting to them)

The problem is I am trying to sum up data by each of the supervisors. The first levels need to include the data from their direct reports. I need to have the data for the second level supervisors include the first levels that report to them, and the non-supervisors that report to them. This goes on through 5 levels of management.

I have tried something like this with mixed results.

SELECT eee.mgr third, ee.mgr sec, e.mgr first, sum(amt) FROM emp eee, emp ee, emp e, data d
where d.id = e.id
and e.mgr = ee.id(+)
and ee.mgr = eee.id(+)
group by 1,2,3

I'm not sure if I'm on the right track.

Any ideas? Is this possible in one query, or do I need separate queries for each level?

I would appreciate any insight.

TIA Received on Thu Jan 06 2000 - 23:20:45 CST