Received: from jupiter.hub.org (jupiter.hub.org [64.49.215.5]) by postgresql.org (Postfix) with ESMTP id 38F2A47510B for ; Tue, 26 Feb 2002 23:32:21 -0500 (EST) Received: (from news@localhost) by jupiter.hub.org (8.11.6/8.11.6) id g1R4KQY07003 for pgsql-sql@postgresql.org; Tue, 26 Feb 2002 22:20:26 -0600 (CST) (envelope-from news) From: Edward Murray X-Newsgroups: comp.databases.postgresql.sql Subject: Left Outer Join Question Date: Wed, 27 Feb 2002 15:18:29 +1100 Organization: Avenue Network Services Lines: 85 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: news@hub.org User-Agent: Pan/0.11.2 (Unix) X-Comment-To: ALL To: pgsql-sql@postgresql.org X-Archive-Number: 200202/414 X-Sequence-Number: 6836 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; Somebody Please Help! Regards Ed Murray Avenue Network Services