Re: left outer join on more than 2 tables? (UNCLASSIFIED)

Classification: UNCLASSIFIED
Caveats: NONE

Maybe I am way of base here, but I see a reference to region in this
query. However, I think count(cm.id) is correct because some would have
a count of 0. Count(*) would produce counts when there are no
complaints. (If I understand the logic, again, I am VERY new to this)

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Rob Sargent
Sent: Tuesday, June 16, 2009 2:40 PM
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] left outer join on more than 2 tables?

Richard Broersma wrote:
> On Tue, Jun 16, 2009 at 1:59 PM, Carol
> Cheung<cacheung(at)consumercontact(dot)com> wrote:
>
>> I would like to find the counts of complaints by region and I would
>> like all regions to be displayed, regardless of whether or not
>> complaints exist for that region. Is left outer join what I'm looking
for?
>>
>
> SELECT R.region_name, Count(*) AS RegionComplaints
> FROM Region AS R
> LEFT JOIN City AS Ci
> ON R.id = C.region_id
> LEFT JOIN Complaint AS Cm
> ON Ci.id = Cm.city_id
> GROUP BY R.region_name;
>
>
>
Is there a city without a reference to region? And wouldn't you want to
count(cm.id)?

--
Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Classification: UNCLASSIFIED
Caveats: NONE