pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Left Outer Join Question
8+ messages / 5 participants
[nested] [flat]

* Left Outer Join Question
@ 2002-02-27 04:18 Edward Murray <mail@avenuedesign.net>
  2002-02-27 05:23 ` Re: Left Outer Join Question Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2002-02-27 06:55 ` Re: Left Outer Join Question Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 2 replies; 8+ messages in thread

From: Edward Murray @ 2002-02-27 04:18 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Left Outer Join Question
  2002-02-27 04:18 Left Outer Join Question Edward Murray <mail@avenuedesign.net>
@ 2002-02-27 05:23 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2002-02-27 05:55   ` Re: Left Outer Join Question Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-27 05:59   ` Re: Left Outer Join Question Edward Murray <mail@avenuedesign.net>
  1 sibling, 2 replies; 8+ messages in thread

From: Christopher Kings-Lynne @ 2002-02-27 05:23 UTC (permalink / raw)
  To: Edward Murray <mail@avenuedesign.net>; pgsql-sql

> 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;

Try using:

count(coalesce(items.recordnum, 0))

instead of:

count(items.recordnum)

I can't guarantee that it will work - I haven't tried it myself.  What's
probably happening is that count isn't counting NULLs?

Chris




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Left Outer Join Question
  2002-02-27 04:18 Left Outer Join Question Edward Murray <mail@avenuedesign.net>
  2002-02-27 05:23 ` Re: Left Outer Join Question Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2002-02-27 05:55   ` Tom Lane <tgl@sss.pgh.pa.us>
  2002-02-27 06:04     ` Re: Left Outer Join Question Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2002-02-27 06:09     ` Re: Left Outer Join Question Edward Murray <mail@avenuedesign.net>
  1 sibling, 2 replies; 8+ messages in thread

From: Tom Lane @ 2002-02-27 05:55 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Edward Murray <mail@avenuedesign.net>; pgsql-sql

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> What's probably happening is that count isn't counting NULLs?

As per spec.  If you want to count records independently of whether
any particular column is NULL or not, use count(*).  See the docs,
notably
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/sql-expressions.html#SYNTAX-AGGREGATES

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Left Outer Join Question
  2002-02-27 04:18 Left Outer Join Question Edward Murray <mail@avenuedesign.net>
  2002-02-27 05:23 ` Re: Left Outer Join Question Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2002-02-27 05:55   ` Re: Left Outer Join Question Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-02-27 06:04     ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  1 sibling, 0 replies; 8+ messages in thread

From: Christopher Kings-Lynne @ 2002-02-27 06:04 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Edward Murray <mail@avenuedesign.net>; pgsql-sql

> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > What's probably happening is that count isn't counting NULLs?
> 
> As per spec.  If you want to count records independently of whether
> any particular column is NULL or not, use count(*).  See the docs,
> notably
> http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/sql-ex
> pressions.html#SYNTAX-AGGREGATES

OK, Edward - I guess Tom means do this?:

select category.name, count(items.*) 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;

Chris




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Left Outer Join Question
  2002-02-27 04:18 Left Outer Join Question Edward Murray <mail@avenuedesign.net>
  2002-02-27 05:23 ` Re: Left Outer Join Question Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2002-02-27 05:55   ` Re: Left Outer Join Question Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-02-27 06:09     ` Edward Murray <mail@avenuedesign.net>
  1 sibling, 0 replies; 8+ messages in thread

From: Edward Murray @ 2002-02-27 06:09 UTC (permalink / raw)
  To: pgsql-sql

On Wed, 27 Feb 2002 16:59:23 +1100, Tom Lane wrote:

> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> What's probably happening is that count isn't counting NULLs?
> 
> As per spec.  If you want to count records independently of whether any
> particular column is NULL or not, use count(*).  See the docs, notably
> http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/sql-expressions.html#SYNTAX-AGGREGATES

I think the problem is that the rows are not being returned by the query
at all not that any particular column is null. Do I need to perform
another outer join? I am not sure how to tackle this problem.


> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Left Outer Join Question
  2002-02-27 04:18 Left Outer Join Question Edward Murray <mail@avenuedesign.net>
  2002-02-27 05:23 ` Re: Left Outer Join Question Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2002-02-27 05:59   ` Edward Murray <mail@avenuedesign.net>
  1 sibling, 0 replies; 8+ messages in thread

From: Edward Murray @ 2002-02-27 05:59 UTC (permalink / raw)
  To: pgsql-sql

Chris,
	This has not fixed it.
	Thanks for your post.
Regards
Ed Murray

>> 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;
> 
> Try using:
> 
> count(coalesce(items.recordnum, 0))
> 
> instead of:
> 
> count(items.recordnum)
> 
> I can't guarantee that it will work - I haven't tried it myself.  What's
> probably happening is that count isn't counting NULLs?
> 
> Chris
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Left Outer Join Question
  2002-02-27 04:18 Left Outer Join Question Edward Murray <mail@avenuedesign.net>
@ 2002-02-27 06:55 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2002-02-28 21:44   ` Re: Left Outer Join Question Andy Marden <amarden@usa.net>
  1 sibling, 1 reply; 8+ messages in thread

From: Stephan Szabo @ 2002-02-27 06:55 UTC (permalink / raw)
  To: Edward Murray <mail@avenuedesign.net>; +Cc: pgsql-sql

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;




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Left Outer Join Question
  2002-02-27 04:18 Left Outer Join Question Edward Murray <mail@avenuedesign.net>
  2002-02-27 06:55 ` Re: Left Outer Join Question Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-02-28 21:44   ` Andy Marden <amarden@usa.net>
  0 siblings, 0 replies; 8+ messages in thread

From: Andy Marden @ 2002-02-28 21:44 UTC (permalink / raw)
  To: 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





^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2002-02-28 21:44 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-27 04:18 Left Outer Join Question Edward Murray <mail@avenuedesign.net>
2002-02-27 05:23 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-02-27 05:55   ` Tom Lane <tgl@sss.pgh.pa.us>
2002-02-27 06:04     ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-02-27 06:09     ` Edward Murray <mail@avenuedesign.net>
2002-02-27 05:59   ` Edward Murray <mail@avenuedesign.net>
2002-02-27 06:55 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-02-28 21:44   ` Andy Marden <amarden@usa.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox