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