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.val2end *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.idand 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
