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

andrew_at_ccc.govt.nz asked:

> 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

p0070621_at_oxford-brookes.ac.uk (Tommy Wareing) suggested:

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

This will not work because the outer join results in a count of 1. Instead try this:

SELECT	source.mesh_source,
	COUNT(*)
FROM	source,
	target
WHERE	source.mesh_source = target.mesh_target
GROUP BY
	source.mesh_source

	0
FROM	source
WHERE NOT EXISTS (
	SELECT	NULL
	FROM	target
	WHERE	source.mesh_source = target.mesh_target

The first part of the UNION is your initial SELECT statement and the second part returns SOURCE rows with no corresponding values in TARGET.

--
 Alvin W. Law .............................................. Oracle Corporation
 Senior Software Engineer ...................... 300 Oracle Parkway, Box 659306
 Manufacturing Applications .......................... Redwood Shores, CA 94065
 Email: alaw_at_oracle.com ........... Voice: 1.415.506.3390 . Fax: 1.415.506.7299