From: mila <me@pierro.dds.nl>
To: pgsql-sql@postgresql.org
Subject: LEFT JOIN and missing values
Date: Wed, 12 Mar 2003 19:24:23 +0100
Message-ID: <158808514272.20030312192423@pierro.dds.nl> (raw)
Hi,
I have a problem, like this:
I have 2 tables, T1 (id, val1) -- a vector
and T2 (id1, id2, val2) -- this is actually a sqare matrix
in the form of a very long table.
I need to do some operations with them, such as take a
dot-product of 2 rows of the matrix (encoded as T2), weighted with T1.. .
The query is the following:
select c.id1, d.id1, sum(c.val2*d.val2*T1.val1)
from T1, T2 c, T2 d
where c.ID2 = T1.id AND d.id2 = T1.id AND c.id1 < d.id1
group by c.id1, d.id1;
This gives me all possible pairs of (weighted) dot-products, as I want them.
Now, some 90% of values in the T2 are the same, say, 0.5, so I could
thouw them away. But then the query above is not valid anymore, since
if there is mo match on the condition c.ID2 = T1.id AND d.id2 =
T1.id, nothing is added (it might be that c.id2 is there and d.id2 is
not!).
I quiess I need a left join + substitute for missing value. Can anyone
help me with the query? I cannot figure it out, too bad ad left
joins...
thanks,
Mila
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: me@pierro.dds.nl
Subject: Re: LEFT JOIN and missing values
In-Reply-To: <158808514272.20030312192423@pierro.dds.nl>
* 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