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-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Rob Sargent Sent: Tuesday, June 16, 2009 2:40 PM To: pgsql-sql@postgresql.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<cache...@consumercontact.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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql Classification: UNCLASSIFIED Caveats: NONE -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
