Message-ID: <3FA2BDA8.60007@klaster.net>
Date: Fri, 31 Oct 2003 20:53:12 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL;
 rv:1.5) Gecko/20030916
MIME-Version: 1.0
To: Shaun Watts <SWatts@computer-systems.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: selecting problems
References: 
 <6A1C87CA83BA8C419B1262B3728B12D102D435@csiserv1.computer-systems.com>
In-Reply-To: 
 <6A1C87CA83BA8C419B1262B3728B12D102D435@csiserv1.computer-systems.com>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 8bit

Dnia 2003-10-31 20:19, Użytkownik Shaun Watts napisał:
> What I am trying to do is select all the rows out of the categories
> table (see below) and select the sum of j_amount out of the judgment
> table.  Right now it is only returning the categories that are in the
> judgment table.  I want it to return those fields with the amounts out
> of the judgment table, but also the all the other categories from the
> categories table with 0 as there amount.  Does anyone have a
> suggestion.
> 
> select ca_code,ca_desc,sum(j_amount) as sum_amt,ca_dis_cycle
> from categories LEFT OUTER JOIN judgment ON
> (j_category=ca_code)
> where j_case_no='45698'
> and j_party_no=1
> group by ca_code,ca_desc,ca_dis_cycle
> 
> Thanks,
> Shaun

This left join won't work. Where clause changes your left join into 
inner one.

select ca_code,ca_desc,sum(j_amount) as sum_amt,ca_dis_cycle
from categories LEFT OUTER JOIN judgment ON
  (j_category=ca_code and j_case_no='45698' and j_party_no=1)
group by ca_code,ca_desc,ca_dis_cycle

or

select ca_code,ca_desc,sum(j_amount) as sum_amt,ca_dis_cycle
from categories LEFT OUTER JOIN judgment ON (j_category=ca_code)
where (j_case_no='45698' and j_party_no=1) or j_category is null
group by ca_code,ca_desc,ca_dis_cycle

Probably you also need to change your sum(j_amount) into:
sum(case when j_amount is null then 0 else j_amount end)

Regards,
Tomasz Myrta