Hi Zsolt Balanyi!

Your LEFT OUTER JOIN does include all records from T1 regardless of
whether there is a link to T2 or not. But it only returns those rows
that have the correct T2.Type - which of course excludes all rows
without a matching T2.

Change your query to

SELECT T1.ID, T1.NAME, COALESCE(SUM(T2.MyVALUE), 0)
FROM T1
LEFT JOIN T2 ON T1.ID = T2.ID AND T2.MyTYPE = :WANTEDTYPE
GROUP BY T1.ID, T1.NAME

(i.e. move any comparisons of T2 from the WHERE to the LEFT JOIN clause)
and I think you'll get a result more similar to what you want.

HTH,
Set
-Firebird Foundation member
-Read more at http://www.firebirdsql.org/index.php?op=ffoundation

Balanyi Zsolt wrote:

> SELECT T1.ID, T1.NAME, COALESCE(SUM(VALUE), 0)
> FROM T1 LEFT OUTER JOIN T2 ON T1.ID = T2.ID
> WHERE
> T2.TYPE = :WANTEDTYPE
> GROUP BY T1.ID, T1.NAME