From: | Edward Murray <mail(at)avenuedesign(dot)net> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Left Outer Join Question |
Date: | 2002-02-27 04:18:29 |
Message-ID: | a5hmq8$6qi$1@jupiter.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | OV | 2002-02-27 04:52:49 | Re: Removing duplicates |
Previous Message | Christopher Kings-Lynne | 2002-02-27 03:17:05 | Re: Timestamp output |