pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: left outer join on more than 2 tables? (UNCLASSIFIED)
4+ messages / 2 participants
[nested] [flat]
* Re: left outer join on more than 2 tables? (UNCLASSIFIED)
@ 2009-06-16 21:27 Hall, Crystal M CTR DISA JITC <Crystal.Hall.ctr@disa.mil>
2 siblings, 0 replies; 4+ messages in thread
From: Hall, Crystal M CTR DISA JITC @ 2009-06-16 21:27 UTC (permalink / raw)
To: pgsql-sql
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@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Carol Cheung
Sent: Tuesday, June 16, 2009 2:00 PM
To: pgsql-sql@postgresql.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@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Classification: UNCLASSIFIED
Caveats: NONE
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: left outer join on more than 2 tables? (UNCLASSIFIED)
@ 2009-06-16 21:52 Hall, Crystal M CTR DISA JITC <Crystal.Hall.ctr@disa.mil>
2 siblings, 1 reply; 4+ messages in thread
From: Hall, Crystal M CTR DISA JITC @ 2009-06-16 21:52 UTC (permalink / raw)
To: pgsql-sql
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@postgresql.org
[mailto:pgsql-sql-owner@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<cacheung@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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: left outer join on more than 2 tables? (UNCLASSIFIED)
@ 2009-06-16 22:54 Richard Broersma <richard.broersma@gmail.com>
parent: Hall, Crystal M CTR DISA JITC <Crystal.Hall.ctr@disa.mil>
0 siblings, 0 replies; 4+ messages in thread
From: Richard Broersma @ 2009-06-16 22:54 UTC (permalink / raw)
To: Hall, Crystal M CTR DISA JITC <Crystal.Hall.ctr@disa.mil>; +Cc: pgsql-sql
On Tue, Jun 16, 2009 at 2:52 PM, Hall, Crystal M CTR DISA
JITC<Crystal.Hall.ctr@disa.mil> wrote:
> 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)
Here is a very good website to visit to get practice with SQL using PostgreSQL.
http://www.sqlzoo.net/select_group_by
If you look up at the upper right corner of the web-page, you can
select PostgreSQL are your RDBMS (other flavors are available also).
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: left outer join on more than 2 tables? (UNCLASSIFIED)
@ 2009-06-17 15:06 Hall, Crystal M CTR DISA JITC <Crystal.Hall.ctr@disa.mil>
2 siblings, 0 replies; 4+ messages in thread
From: Hall, Crystal M CTR DISA JITC @ 2009-06-17 15:06 UTC (permalink / raw)
To: pgsql-sql
Classification: UNCLASSIFIED
Caveats: NONE
My problem with that is that you are counting rows per region and you have done a left jion on region. That means there will be at least one row per region even if there are 0 compliants. It might yield the same result now, but if you even have a period where a region recieves no complaints they will have a complaint count of 1 instead of 0. That is just my guess based on the logic.
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Richard Broersma
Sent: Tuesday, June 16, 2009 3:50 PM
To: Rob Sargent
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] left outer join on more than 2 tables?
On Tue, Jun 16, 2009 at 2:40 PM, Rob Sargent<robjsargent@gmail.com> wrote:
> Is there a city without a reference to region?
I don't know, but the OP wanted to know complaints by region.
> And wouldn't you want to count(cm.id)?
Count(cm.id) and Count(*) produce the same result. But I like
Count(*) more since it helps to correctly express the idea that we are counting rows per group and not cm.id(s) per group.
--
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
Classification: UNCLASSIFIED
Caveats: NONE
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2009-06-17 15:06 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-06-16 21:27 ` Hall, Crystal M CTR DISA JITC <Crystal.Hall.ctr@disa.mil>
2009-06-16 21:52 ` Hall, Crystal M CTR DISA JITC <Crystal.Hall.ctr@disa.mil>
2009-06-16 22:54 ` Richard Broersma <richard.broersma@gmail.com>
2009-06-17 15:06 ` Hall, Crystal M CTR DISA JITC <Crystal.Hall.ctr@disa.mil>
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