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

From: <fgreene_at_hayes.com>
Date: 25 Aug 93 12:33:19 EDT
Message-ID: <7846.2c7b5c10_at_hayes.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. Received on Wed Aug 25 1993 - 18:33:19 CEST

Original text of this message