Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 070F9634AA2 for ; Tue, 16 Jun 2009 18:50:16 -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 67758-06-5 for ; Tue, 16 Jun 2009 18:50:08 -0300 (ADT) X-Greylist: delayed 00:10:04.577671 by SQLgrey-1.7.6 Received: from mail.eclinic.com.au (blowfish.eclinic.com.au [202.134.35.74]) by mail.postgresql.org (Postfix) with ESMTP id 7D6566351A0 for ; Tue, 16 Jun 2009 18:50:01 -0300 (ADT) Received: from mail.eclinic.com.au (preston [127.0.0.1]) by mail.eclinic.com.au (Postfix) with ESMTP id DE58E239568B; Wed, 17 Jun 2009 07:39:51 +1000 (EST) Received: from mail.eclinic.com.au (preston [127.0.0.1]) by mail.eclinic.com.au (Postfix) with ESMTP id CCC3623E1DA8; Wed, 17 Jun 2009 07:39:51 +1000 (EST) Received: from [192.168.1.195] (CPE-121-223-230-214.static.vic.bigpond.net.au [121.223.230.214]) by mail.eclinic.com.au (Postfix) with ESMTPA id 69CA6239844B; Wed, 17 Jun 2009 07:39:51 +1000 (EST) From: Gavin Baumanis To: Richard Broersma In-Reply-To: <396486430906161425i1cb7949enff5443cc2043805a@mail.gmail.com> Subject: Re: left outer join on more than 2 tables? References: <4A3807CF.90109@consumercontact.com> <396486430906161425i1cb7949enff5443cc2043805a@mail.gmail.com> Message-Id: Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit Mime-Version: 1.0 (Apple Message framework v935.3) Date: Wed, 17 Jun 2009 07:39:50 +1000 Cc: Carol Cheung , pgsql-sql@postgresql.org X-Mailer: Apple Mail (2.935.3) X-AV-Checked: ClamAV using ClamSMTP 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/77 X-Sequence-Number: 32682 At the risk of being wrong.... (I'm always ready to learn something new) - and seemingly I'm only too happy to be wrong!... And... it might even be that it is exactly the same result - but I would have proposed; SELECT R.region_name, Count(*) AS RegionComplaints FROM Region AS R LEFT JOIN City AS Ci LEFT JOIN Complaint AS Cm ON Ci.id = Cm.city_id ON R.id = C.region_id GROUP BY R.region_name; Gavin. On 17/06/2009, at 7:25 AM, Richard Broersma wrote: > On Tue, Jun 16, 2009 at 1:59 PM, Carol > Cheung 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; > > > -- > Regards, > Richard Broersma Jr. > > Visit the Los Angeles PostgreSQL Users Group (LAPUG) > http://pugs.postgresql.org/lapug > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql As always Please contact me if I can be of any further assistance. Gavin "Beau" Baumanis Senior Application Developer PalCare P/L 657 Nicholson Street Carlton North Victoria, Australia, 3054 E: beau@palcare.com.au P: +61 -3 9380 3513 M: +61 -438 545 586 W: http://www.palcare.com.au