Re: LEFT JOIN and missing values

Tomasz,

> select c.id1, d.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
> left join T2 c on (c.ID2 = T1.id)
> left join T2 d on (d.id2 = T1.id and (c.id1 < d.id1 or c.id1 is null))
> group by c.id1, d.id1;

> I'm not sure about this query... There is a problem - is it possible to
> have missing values in your matrix? This solution replaces all missing
> values into 0.5.
yes, that's what I want to do, remove the tuples with default value
and substitute it later if I need it.

> What should happen to "T2 alias d" if there is missing
> value in "T2 alias c"?

if a value, c.val2, is missing, it should be replaced with 0.5, since the only
deleted tuples are those that had contained 0.5 in "val2" field. The
(other) value, d.val2, that exisits in the matrix, should be used "as is". If
they are both missing, then t1.val1 is multiplied twice by 0.5

> What happens then to clause "c.id1<d.id1" if "c"
> is not found?

I removed this condition "c.id1 < d.id1", since it is indeed
unclear, what to compare when a row is "missing" (I can filter it
later).

Then, I tried the query that you proposed,
on a test table T1 - 10 rows,
T2 = 10x10 = 100 rows.

With the full T2 the query is equivalent to the one that I gave, but
when I remove the tuples with default values (call it table T3), then
the resulting table is only 90 rows instead of 100, besides, the
values are different from the "right" ones from "full" T2.

The missing combinations seem to be random.

How can that be if T1 contains all values that might be in T2.id2,
T2.id1?
and what do do next?

Mila