Обсуждение: 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
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_idGROUP BY R.region_name; -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
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
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)?
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<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; > > > -- > 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
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
On Tue, Jun 16, 2009 at 2:39 PM, Gavin Baumanis<gavinb@eclinic.com.au> wrote: > 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; Yup, it produces the same result. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
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
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
Richard Broersma wrote: > 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. > > I didn't try this, but with regionless cities, you may need a full join if you want a complete accounting of all complaints, some being logged to the null 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. > > > > "Same result" is not true. I loaded tables. Using count(*) you get count=1 for regions without complaints. Using count(complaint.id) you get count = 0. (The deference amount to counting the left hand side (region) vs the right hand side (complaint) which I believe is what OP is after).
On 16/06/2009 19:12, Rob Sargent wrote the following: > Richard Broersma wrote: >> 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. >> >> > I didn't try this, but with regionless cities, you may need a full join > if you want a complete accounting of all complaints, some being logged > to the null 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. >> >> >> >> > > "Same result" is not true. I loaded tables. > Using count(*) you get count=1 for regions without complaints. Using > count(complaint.id) you get count = 0. (The deference amount to > counting the left hand side (region) vs the right hand side (complaint) > which I believe is what OP is after). > Thanks everyone for your help. Your solutions worked. Much appreciated. - Carol
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 therewill be at least one row per region even if there are 0 compliants. It might yield the same result now, but if youeven have a period where a region recieves no complaints they will have a complaint count of 1 instead of 0. That isjust 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