Re: HELP!: How to get 0 COUNT values from a join

andrew_at_ccc.govt.nz wrote:
> Hi folks,
 

> Here's the situation:
> create table source ( ..., mesh_source, ... );
> create table target ( ..., mesh_target, ... );
> I've tried:
 

> select
> source.mesh_source,
> count(*)
> from
> source,
> target
> where
> source.mesh_source = target.mesh_target
> group by
> source.mesh_source;
 

> 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".

> Cheers
 

> Andrew

--
  _________________________   ________________________________
 /  Tommy Wareing          \ /  Dying ain't much of a living, \
|  p0070621_at_brookes.ac.uk   X   When you're out and on the run,|
 \  0865-483389            / \  Dying ain't much of a living,  |
  ~~~~~~~~~~~~~~~~~~~~~~~~~   \ When you're young             /
                               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~