Received: from jupiter.hub.org (jupiter.hub.org [64.49.215.5]) by postgresql.org (Postfix) with ESMTP id 585EE475BC8 for ; Thu, 28 Feb 2002 17:02:50 -0500 (EST) Received: (from news@localhost) by jupiter.hub.org (8.11.6/8.11.6) id g1SLjoP63225 for pgsql-sql@postgresql.org; Thu, 28 Feb 2002 15:45:50 -0600 (CST) (envelope-from news) From: "Andy Marden" X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Left Outer Join Question Date: Thu, 28 Feb 2002 21:44:59 -0000 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 129 Message-ID: References: <20020226224431.K98706-100000@megazone23.bigpanda.com> X-Complaints-To: news@hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-Mimeole: Produced By Microsoft MimeOLE V6.00.2600.0000 To: pgsql-sql@postgresql.org X-Archive-Number: 200202/460 X-Sequence-Number: 6881 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" 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