Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 514A54759ED for ; Wed, 27 Feb 2002 01:55:34 -0500 (EST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 56A96D5F9; Tue, 26 Feb 2002 22:55:37 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 4C2DA5BFB; Tue, 26 Feb 2002 22:55:37 -0800 (PST) Date: Tue, 26 Feb 2002 22:55:37 -0800 (PST) From: Stephan Szabo To: Edward Murray Cc: Subject: Re: Left Outer Join Question In-Reply-To: Message-ID: <20020226224431.K98706-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200202/422 X-Sequence-Number: 6843 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;