desperately need ur help!

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! Received on Tue Mar 26 2002 - 12:04:20 CST