Re: Left Outer Join Question

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(at)megazone23(dot)bigpanda(dot)com> wrote in message
news:20020226224431(dot)K98706-100000(at)megazone23(dot)bigpanda(dot)com(dot)(dot)(dot)
> 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