From: | mila <me(at)pierro(dot)dds(dot)nl> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | LEFT JOIN and missing values |
Date: | 2003-03-12 18:24:23 |
Message-ID: | 158808514272.20030312192423@pierro.dds.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tomasz Myrta | 2003-03-12 18:47:07 | Re: LEFT JOIN and missing values |
Previous Message | Manfred Koizar | 2003-03-12 16:24:48 | Re: SERIAL does not ROLLBACK |