From: Carol Cheung <cacheung@consumercontact.com>
To: pgsql-sql@postgresql.org
Subject: left outer join on more than 2 tables?
Date: Tue, 16 Jun 2009 16:59:59 -0400
Message-ID: <4A3807CF.90109@consumercontact.com> (raw)
Hi,
I have 3 tables
region:
id
region_name
city:
id
city_name
region_id
complaint:
id
date
city_id
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?
I'm stuck at this point:
select r.region_name, count(1) from region r left outer join city c,
complaint k on (k.city_id = c.id and r.id = c.region_id) group by
r.region_name
Of course this doesn't work ...
Can anyone provide their insight as to how I can achieve this?
Thanks in advance,
C
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cacheung@consumercontact.com
Subject: Re: left outer join on more than 2 tables?
In-Reply-To: <4A3807CF.90109@consumercontact.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox