Re: Left Outer Join Question
От | Andy Marden |
---|---|
Тема | Re: Left Outer Join Question |
Дата | |
Msg-id | a5m8ed$1thv$1@jupiter.hub.org обсуждение исходный текст |
Ответ на | Re: Left Outer Join Question (Stephan Szabo <sszabo@megazone23.bigpanda.com>) |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: