Re: desperately need ur help!

From: news.sovintel.ru <valery_tsekhmister_at_hotmail.com>
Date: Tue, 26 Mar 2002 21:40:35 +0300
Message-ID: <a7qe5f$jdm$1@josh.sovintel.ru>

Hi Henry !

If you don't want to use outer join, you might utilize in-line function instead.
For ex.: you query might look like this:

select

dept.number,
(select nvl(sum(emp.salary),0) from emp where emp.number in (select employee from emp_dept where dept = dept.number) ) sum_salary
from dept

This query will return zero if there are no employees in particular department.

Frankly, you can use outer join nonetheless, in this case your query will be:

select

dept.number
,nvl(sum(emp.salary),0) sum_salary
from

   dept                t1
  ,emp_dept        t2
  ,emp                t3

where

t1.dept(+) = t2.number and
t3.number(+) = t2.employee

In some cases, this query will work a bit slower.

Regards !
Valery.

"Henry" <hxzhang_at_binary-solutions.net> wrote in message news:EK2o8.2397$8f5.37440_at_news0.telusplanet.net...
> 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:40:35 CST