[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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] left outer join on more than 2 tables?
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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] 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: [email protected] [mailto:[email protected]] On Behalf Of Carol Cheung Sent: Tuesday, June 16, 2009 2:00 PM To: [email protected] 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql Classification: UNCLASSIFIED Caveats: NONE -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] left outer join on more than 2 tables?
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; Is there a city without a reference to region? And wouldn't you want to count(cm.id)? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] left outer join on more than 2 tables?
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 ([email protected]) 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: [email protected] P: +61 -3 9380 3513 M: +61 -438 545 586 W: http://www.palcare.com.au -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] left outer join on more than 2 tables? (UNCLASSIFIED)
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: [email protected] [mailto:[email protected]] On Behalf Of Rob Sargent Sent: Tuesday, June 16, 2009 2:40 PM To: [email protected] 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 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql Classification: UNCLASSIFIED Caveats: NONE -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] left outer join on more than 2 tables?
On Tue, Jun 16, 2009 at 2:39 PM, Gavin Baumanis 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 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] left outer join on more than 2 tables?
On Tue, Jun 16, 2009 at 2:40 PM, Rob Sargent 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] left outer join on more than 2 tables?
Richard Broersma wrote: On Tue, Jun 16, 2009 at 2:40 PM, Rob Sargent 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). -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] left outer join on more than 2 tables? (UNCLASSIFIED)
On Tue, Jun 16, 2009 at 2:52 PM, Hall, Crystal M CTR DISA JITC 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 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
