pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLeft Outer Join Question
8+ messages / 5 participants
[nested] [flat]
* Left Outer Join Question
@ 2002-02-27 04:18 Edward Murray <mail@avenuedesign.net>
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 05:23 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: 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 05:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
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 05:59 Edward Murray <mail@avenuedesign.net>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
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 06:04 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
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 06:09 Edward Murray <mail@avenuedesign.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
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 06:55 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Edward Murray <mail@avenuedesign.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-28 21:44 Andy Marden <amarden@usa.net>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
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