Message-ID: <3E6F9B31.7050403@klaster.net>
Date: Wed, 12 Mar 2003 21:40:17 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; PL; rv:1.1) Gecko/20020826
MIME-Version: 1.0
To: mila <me@pierro.dds.nl>
Cc: pgsql-sql@postgresql.org
Subject: Re: LEFT JOIN and missing values
References: <158808514272.20030312192423@pierro.dds.nl>
	<3E6F80AB.8040709@klaster.net>
	<147814986879.20030312211215@pierro.dds.nl>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

I looked at your example once again and I noticed that wrong result 
comes from "group by" clause. If we delete useless rows with value 
"0.5", we also remove information about possible values of t2.id1.

Let's say your table t2 is full of 0.5 values. It means, we can empty 
it. If we do this, how Postgres would know which t2.id1 values to 
return? What size of matrix should it return?

I think if you really need to remove your 0.5 values, you need another 
table with all possible values of id1. This table will look like:

create table t3
(
   id1 integer;
);

your query should look then:

select cc.id1, dd.id1, sum(
  case when c.id2 is null then 0.5 else c.val2 end *
  case when d.id2 is null then 0.5 else d.val2 end *T1.val1)
from
  T1
  cross join T3 cc
  left join T2 c on (c.ID2 = T1.id and c.id1=cc.id1)
  cross join T3 dd
  left join T2 d on (d.id2 = T1.id and d.id1=dd.id1)
group by cc.id1, dd.id1;

Anyway I'm not sure if it is what you really want :-(

Regards,
Tomasz Myrta