Обсуждение: Left Outer Join Question
I seem to have reached the limits of my (postgres)SQL knowledge. If anybody could help me out of this predicament it would be greatly appreciated. I know the explanation of this problem may seem a bit complex but I am hoping the answer may be quite simple. I have items represented for sale on a web-site. I have a number of sections which each have sub-categories under them and each category may contain items. Each item is posted by a shop with a client number. I am trying to list the sub-categories of a section and the number of items posted in by a particular shop in each of those categories. Simplified versions of the tables are as such: sections (recordnum intname varchar ) category (recordnum intname varchar ) section_subcats (sectionum intcatnum int ) items (recordnum intcatnum intclientnum intname varchar ) The categories are assigned to the sections via the Section_subcats table using data in the tables as such: section 1 Fruit category 1 Apple 2 Pear 3 Orange section_subcats 1 1 1 2 1 3 items 1 1 333 'Jonathan' 2 1 333 'Fuji' 3 1 444 'Granny Smith' I am trying to construct a query which will return something like the following for clientnum 333: Fruit ----- Apple 2 Pear 0 Orange 0 I have tried the following query but the results with no items are excluded: select category.name, count(items.recordnum) from category left outer join items on (category.recordnum = items.catnum) where category.recordnum = section_subcats.catnum and section_subcats.sectionnum = 1 and items.clientnum = 333 group by category.name; Somebody Please Help! Regards Ed Murray Avenue Network Services
> select category.name, count(items.recordnum) from category left outer join > items on (category.recordnum = items.catnum) where category.recordnum = > section_subcats.catnum and section_subcats.sectionnum = 1 and > items.clientnum = 333 group by category.name; Try using: count(coalesce(items.recordnum, 0)) instead of: count(items.recordnum) I can't guarantee that it will work - I haven't tried it myself. What's probably happening is that count isn't counting NULLs? Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes: > What's probably happening is that count isn't counting NULLs? As per spec. If you want to count records independently of whether any particular column is NULL or not, use count(*). See the docs, notably http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/sql-expressions.html#SYNTAX-AGGREGATES regards, tom lane
Chris,This has not fixed it.Thanks for your post. Regards Ed Murray >> select category.name, count(items.recordnum) from category left outer >> join items on (category.recordnum = items.catnum) where >> category.recordnum = section_subcats.catnum and >> section_subcats.sectionnum = 1 and items.clientnum = 333 group by >> category.name; > > Try using: > > count(coalesce(items.recordnum, 0)) > > instead of: > > count(items.recordnum) > > I can't guarantee that it will work - I haven't tried it myself. What's > probably happening is that count isn't counting NULLs? > > Chris > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes: > > What's probably happening is that count isn't counting NULLs? > > As per spec. If you want to count records independently of whether > any particular column is NULL or not, use count(*). See the docs, > notably > http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/sql-ex > pressions.html#SYNTAX-AGGREGATES OK, Edward - I guess Tom means do this?: select category.name, count(items.*) from category left outer join items on (category.recordnum = items.catnum) where category.recordnum = section_subcats.catnum and section_subcats.sectionnum = 1 and items.clientnum = 333 group by category.name; Chris
On Wed, 27 Feb 2002 16:59:23 +1100, Tom Lane wrote: > "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes: >> What's probably happening is that count isn't counting NULLs? > > As per spec. If you want to count records independently of whether any > particular column is NULL or not, use count(*). See the docs, notably > http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/sql-expressions.html#SYNTAX-AGGREGATES I think the problem is that the rows are not being returned by the query at all not that any particular column is null. Do I need to perform another outer join? I am not sure how to tackle this problem. > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On Wed, 27 Feb 2002, Edward Murray wrote: > I seem to have reached the limits of my (postgres)SQL knowledge. If > anybody could help me out of this predicament it would be greatly > appreciated. I know the explanation of this problem may seem a bit complex > but I am hoping the answer may be quite simple. > > I have items represented for sale on a web-site. I have a number of > sections which each have sub-categories under them and each category may > contain items. > > Each item is posted by a shop with a client number. > > I am trying to list the sub-categories of a section and the number of > items posted in by a particular shop in each of those categories. > > Simplified versions of the tables are as such: > > sections ( > recordnum int > name varchar > ) > > category ( > recordnum int > name varchar > ) > > > > section_subcats ( > sectionum int > catnum int > ) > > items ( > recordnum int > catnum int > clientnum int > name varchar > ) > > The categories are assigned to the sections via the Section_subcats table > using data in the tables as such: > > > section > 1 Fruit > > category > 1 Apple > 2 Pear > 3 Orange > > > section_subcats > 1 1 > 1 2 > 1 3 > > items > 1 1 333 'Jonathan' > 2 1 333 'Fuji' > 3 1 444 'Granny Smith' > I am trying to construct a query which will return something like the > following for clientnum 333: > > > Fruit > ----- > Apple 2 > Pear 0 > Orange 0 > > I have tried the following query but the results with no items are > excluded: > > select category.name, count(items.recordnum) from category left outer join > items on (category.recordnum = items.catnum) where category.recordnum = > section_subcats.catnum and section_subcats.sectionnum = 1 and > items.clientnum = 333 group by category.name; Well, IIRC, by testing items.clientnum=333 you're getting rid of the outerness of the join since those will be NULL in the rows so you're just throwing them back out. Maybe: select category.name, count(foo.recordnum) from category inner join section_subcats on (category.recordnum=section_subcats.catnum) left outer join (select * from items where clientnum=333) foo on (category.recordnum=foo.catnum) where section_subcats.sectionnum=1 group by category.name;
Can you do: select category.name, count(foo.recordnum) from category inner join section_subcats on (category.recordnum=section_subcats.catnum) left outer join items foo on (category.recordnum=foo.catnum) and foo.client_num=333 where section_subcats.sectionnum=1 group by category.name; following the basic Oracle-like principle (with its (+) operator): o if you put a condition in the on clause it tests before the join is done o If you put it in the where clause, it's done after. Hope that's true in PostgreSQL, cos then it's easy and maybe it should be made clearer Cheers Andy "Stephan Szabo" <sszabo@megazone23.bigpanda.com> wrote in message news:20020226224431.K98706-100000@megazone23.bigpanda.com... > On Wed, 27 Feb 2002, Edward Murray wrote: > > > I seem to have reached the limits of my (postgres)SQL knowledge. If > > anybody could help me out of this predicament it would be greatly > > appreciated. I know the explanation of this problem may seem a bit complex > > but I am hoping the answer may be quite simple. > > > > I have items represented for sale on a web-site. I have a number of > > sections which each have sub-categories under them and each category may > > contain items. > > > > Each item is posted by a shop with a client number. > > > > I am trying to list the sub-categories of a section and the number of > > items posted in by a particular shop in each of those categories. > > > > Simplified versions of the tables are as such: > > > > sections ( > > recordnum int > > name varchar > > ) > > > > category ( > > recordnum int > > name varchar > > ) > > > > > > > > section_subcats ( > > sectionum int > > catnum int > > ) > > > > items ( > > recordnum int > > catnum int > > clientnum int > > name varchar > > ) > > > > The categories are assigned to the sections via the Section_subcats table > > using data in the tables as such: > > > > > > section > > 1 Fruit > > > > category > > 1 Apple > > 2 Pear > > 3 Orange > > > > > > section_subcats > > 1 1 > > 1 2 > > 1 3 > > > > items > > 1 1 333 'Jonathan' > > 2 1 333 'Fuji' > > 3 1 444 'Granny Smith' > > I am trying to construct a query which will return something like the > > following for clientnum 333: > > > > > > Fruit > > ----- > > Apple 2 > > Pear 0 > > Orange 0 > > > > I have tried the following query but the results with no items are > > excluded: > > > > select category.name, count(items.recordnum) from category left outer join > > items on (category.recordnum = items.catnum) where category.recordnum = > > section_subcats.catnum and section_subcats.sectionnum = 1 and > > items.clientnum = 333 group by category.name; > > Well, IIRC, by testing items.clientnum=333 you're getting rid of the > outerness of the join since those will be NULL in the rows so you're > just throwing them back out. > > Maybe: > select category.name, count(foo.recordnum) from category inner join > section_subcats on (category.recordnum=section_subcats.catnum) left outer > join (select * from items where clientnum=333) foo on > (category.recordnum=foo.catnum) where section_subcats.sectionnum=1 group > by category.name; > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster