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

Classification: UNCLASSIFIED
Caveats: NONE

Pardon my lameness, I have JUST dipped my toes in PostgreSQL and want to
try this out! I'm probably wrong but here goes my very first PostgreSQL
join attempt!

SELECT region_name, count(complaint.id)
FROM region LEFT JOIN city ON (region.id = city.region_id)
LEFT JOIN complaint ON (city.id = complaint.city_id)
GROUP BY region_name;

Okay, I have been looking at PostgreSQL for all of a few hours today and
don't even have it installed. Am I close, gurus?

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

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

--
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