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

you can try to use the following expression (It's not the solution, 'cause I don't understand well your problem, but the following can help...)

For an hierarchical structure, you can use:

SELECT ENAME, EMPNO, JOB, MGR,
FROM EMP
connect by MGR = prior ENPNO
START WITH ENAME = 'KING' the "connect by ... prior" defines an hierarchical structure, and you can have the LEVEL number back with (level):

SELECT level, ENAME, EMPNO, JOB, MGR,
FROM EMP
connect by MGR = prior ENPNO
START WITH ENAME = 'KING' To select a certain level (ex: 3), and the levels under, you can use:

SELECT level, ENAME, EMPNO, JOB, MGR,
FROM EMP
where level < 3,
connect by MGR = prior ENPNO
START WITH ENAME = 'KING' Hope you have enough...

• François.

zpayne <jakefound_at_hotmail.com> wrote in message news:853th9$m9h$3_at_bgtnsc02.worldnet.att.net...
> 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 - 03:11:50 CST