Date: Wed, 12 Mar 2003 19:24:23 +0100
From: mila <me@pierro.dds.nl>
Message-ID: <158808514272.20030312192423@pierro.dds.nl>
To: pgsql-sql@postgresql.org
Subject: LEFT JOIN and missing values
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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