From: Tomasz Myrta <jasiek@klaster.net>
To: mila <me@pierro.dds.nl>
Cc: pgsql-sql@postgresql.org
Subject: Re: LEFT JOIN and missing values
Date: Wed, 12 Mar 2003 21:40:17 +0100
Message-ID: <3E6F9B31.7050403@klaster.net> (raw)
References: <158808514272.20030312192423@pierro.dds.nl>
<3E6F80AB.8040709@klaster.net>
<147814986879.20030312211215@pierro.dds.nl>
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
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: jasiek@klaster.net, me@pierro.dds.nl
Subject: Re: LEFT JOIN and missing values
In-Reply-To: <3E6F9B31.7050403@klaster.net>
* 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