pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Edward Murray <mail@avenuedesign.net>
To: pgsql-sql@postgresql.org
Subject: Left Outer Join Question
Date: Wed, 27 Feb 2002 15:18:29 +1100
Message-ID: <a5hmq8$6qi$1@jupiter.hub.org> (raw)

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



view thread (8+ messages)  latest in thread

Message-ID: <a5hmq8$6qi$1@jupiter.hub.org>
Permalink:  ../a5hmq8$6qi$1@jupiter.hub.org/
Also on:    postgresql.org/message-id/a5hmq8$6qi$1@jupiter.hub.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: mail@avenuedesign.net
  Subject: Re: Left Outer Join Question
  In-Reply-To: <a5hmq8$6qi$1@jupiter.hub.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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