SQL help please, it shouldn't be this hard.
Date: Thu, 6 Jan 2000 22:20:45 -0700
Message-ID: <853th9$m9h$3_at_bgtnsc02.worldnet.att.net>
[Quoted] [Quoted] I'm trying to write a query that for some reason seems harder than it should be.
[Quoted] [Quoted] 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
[Quoted] non-supervisors reporting to them)
[Quoted] 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 [Quoted] 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 [Quoted] on through 5 levels of management.
[Quoted] 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 [Quoted] each level?
[Quoted] I would appreciate any insight.
TIA Received on Fri Jan 07 2000 - 06:20:45 CET