DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject:references
	:in-reply-to:content-type:content-transfer-encoding;
	b=BsJWUGttCWZo9QcFSJuZKL+Hz4FM/QpnyP9gdDySdtQsBqSXzCM/apyi/9ZW7SqvY6
	XVsV4JkxTBkQqkVxwMhQT/wDCqPhXb1Xt/MA38ymmkUAw9znqHrrOhVgQhbRnciXZXts
	frS6agoltLQW3ulT6CiRz4dLHDWtZFZqsKjCs=
Message-ID: <4A3826FB.8090907@gmail.com>
Date: Tue, 16 Jun 2009 17:12:59 -0600
From: Rob Sargent <robjsargent@gmail.com>
User-Agent: Thunderbird 2.0.0.21 (X11/20090318)
MIME-Version: 1.0
To: Richard Broersma <richard.broersma@gmail.com>,
 pgsql-sql@postgresql.org
Subject: Re: left outer join on more than 2 tables?
References: <4A3807CF.90109@consumercontact.com>	
	<396486430906161425i1cb7949enff5443cc2043805a@mail.gmail.com>	
	<4A38114B.7090106@gmail.com>
	<396486430906161550v30befebelcfddd58ebc4b4f2d@mail.gmail.com>
In-Reply-To: <396486430906161550v30befebelcfddd58ebc4b4f2d@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Richard Broersma wrote:
> On Tue, Jun 16, 2009 at 2:40 PM, Rob Sargent<robjsargent@gmail.com> wrote:
>
>   
>> Is there a city without a reference to region?
>>     
>
> I don't know, but the OP wanted to know complaints by region.
>
>   
I didn't try this, but with regionless cities, you may need a full join
if you want a complete accounting of all complaints, some being logged
to the null region.


>
>
>   
>>  And wouldn't you want to count(cm.id)?
>>     
>
> Count(cm.id) and Count(*) produce the same result.  But I like
> Count(*) more since it helps to correctly express the idea that we are
> counting rows per group and not cm.id(s) per group.
>
>
>
>   

"Same result" is not true.  I loaded tables. 

Using count(*) you get count=1 for regions without complaints.  Using 
count(complaint.id) you get count = 0.  (The deference amount to 
counting the left hand side (region) vs the right hand side (complaint) 
which I believe is what OP is after).