Re: desperately need ur help!

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 26 Mar 2002 19:25:01 +0100
Message-ID: <t3f1aukm9truq4kvpnfchmdgenhp7amrds@4ax.com>

On Tue, 26 Mar 2002 18:04:20 GMT, "Henry" <hxzhang_at_binary-solutions.net> wrote:

>Forgive me for posting it here, but I really can't get any answer elsewhere.
>:-(
>
>I have two entities: EMP and DEPT, for employee and department respectively,
>
>Each employee has a unique number, and a monthly salary.
>Each department has a unique number.
>
>There is also a association bewteen EMP and DEPT (each employee may belong
>to multiple department). EMP_DEPT
>
>Now i want to query for each department, what is the total monthly salary
>for all the employees in that department, I am using :
>
>"select dept.number, sum(emp.salary) from dept, emp where emp.number in
>(select employee from emp_dept where dept = dept.number) group by
>dept.number"
>
>However, this only works when for some department, there is at least one
>employee in it. If there is no employee in a department, that department
>doesn't show up at all. My boss want the department with no employee should
>also appear in the result, with "0" as the total employee salary.
>
>
>Anybody tell me how can i accomplish it? (I can't use outer join here
>because the login is "IN" )
>
>thanx a lot!
>
>
>
>
>
>
>

select dept.number

       , nvl(sum(emp.salary),0)
from dept d,

         emp_dept ed,
         emp e

where ed.number(+) = d.number
and e.empno = ed.empno

You can't use a subquery here.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Mar 26 2002 - 12:25:01 CST