Re: HELP!: How to get 0 COUNT values from a join
From: <jl34778_at_corp02.d51.lilly.com>
Date: 25 Aug 93 16:07:59 EST
Message-ID: <1993Aug25.160759.1_at_corp02.d51.lilly.com>
>
> Haven't tried it, but this should work:
> select source.mesh_source, count(*)
> from source, target
> where source.mesh_source = target.mesh_target(+)
> group by source.mesh_source;
>
> SORRY ANDREW, THAT WON'T DO IT. IN THE CASE WHERE THERE IS NO MATCHING
> VALUE IN THE TARGET TABLE, THE OUTER JOIN WILL RETURN A COUNT OF 1 AND
> NOT ZERO. THIS MATCHES THE CASE WHERE THERE IS EXACTLY ONE MATCH.
>
Date: 25 Aug 93 16:07:59 EST
Message-ID: <1993Aug25.160759.1_at_corp02.d51.lilly.com>
In article <CC980r.Gyu_at_uk.ac.brookes>, p0070621_at_oxford-brookes.ac.uk (Tommy Wareing) writes:
> andrew_at_ccc.govt.nz wrote:
>
>> What I want is:
>> SOURCE.MESH_SOURCE COUNT(*) >> ------------------ -------- >> 2134500 12 >> 2367000 34 >> 2891200 0 ( No corresponding values in "target" ) >> 2397900 219 >> Can this be done ??? *Any* solution(s) would be welcome.
>
> Haven't tried it, but this should work:
> select source.mesh_source, count(*)
> from source, target
> where source.mesh_source = target.mesh_target(+)
> group by source.mesh_source;
>
>> Note the use of the outer join (the "(+)"). It effectively means "return a >> row for this, even if there's no match". >>
> SORRY ANDREW, THAT WON'T DO IT. IN THE CASE WHERE THERE IS NO MATCHING
> VALUE IN THE TARGET TABLE, THE OUTER JOIN WILL RETURN A COUNT OF 1 AND
> NOT ZERO. THIS MATCHES THE CASE WHERE THERE IS EXACTLY ONE MATCH.
>
If you use COUNT on the target table, rather than on the result set, it works.
SQL> l
1 select dept.deptno, count(emp.deptno)
2 from dept, emp
3 where dept.deptno = emp.deptno(+)
4* group by dept.deptno
SQL> /
DEPTNO COUNT(EMP.DEPTNO)
---------- -----------------
10 68
20 46
30 46
40 0
--
Bob Swisshelm | swisshelm_at_Lilly.com | 317 276 5472
Eli Lilly and Company | Lilly Corporate Center | Indianapolis, IN 46285
Received on Wed Aug 25 1993 - 23:07:59 CEST