Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 7C74B634476 for ; Wed, 17 Jun 2009 10:08:12 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 52087-07 for ; Wed, 17 Jun 2009 10:08:01 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.consumercontact.com (mail.consumercontact.com [209.167.202.12]) by mail.postgresql.org (Postfix) with ESMTP id 972A4633706 for ; Wed, 17 Jun 2009 10:08:09 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.consumercontact.com (Postfix) with ESMTP id CB1AC5904A1 for ; Wed, 17 Jun 2009 09:08:05 -0400 (EDT) X-Virus-Scanned: amavisd-new at consumercontact.com Received: from mail.consumercontact.com ([127.0.0.1]) by localhost (mail.consumercontact.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id STGErmundHbD for ; Wed, 17 Jun 2009 09:08:05 -0400 (EDT) Received: from [192.168.1.47] (mordred.consumercontact.com [192.168.1.47]) by mail.consumercontact.com (Postfix) with ESMTP id 6C4F359048E for ; Wed, 17 Jun 2009 09:08:05 -0400 (EDT) Message-ID: <4A38EA8B.5000403@consumercontact.com> Date: Wed, 17 Jun 2009 09:07:23 -0400 From: Carol Cheung User-Agent: Thunderbird 2.0.0.16 (Windows/20080708) MIME-Version: 1.0 To: 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> <4A3826FB.8090907@gmail.com> In-Reply-To: <4A3826FB.8090907@gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200906/83 X-Sequence-Number: 32688 On 16/06/2009 19:12, Rob Sargent wrote the following: > Richard Broersma wrote: >> On Tue, Jun 16, 2009 at 2:40 PM, Rob Sargent >> 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). > Thanks everyone for your help. Your solutions worked. Much appreciated. - Carol