Dmitry Yemanov wrote:

>
> Balanyi Zsolt wrote:
> >
> > I have two tables, T1 and T2, which are linked by an ID (integer). T2
> > has an ID, a TYPE field, which describes the type of the row, and a
> > VALUE field.
> > I am interested in all (or some - not important) rows of T1 and the
> > SUM(VALUE) of T2 that has a given TYPE and is linked to T1.
> > My query looks like this:
> > 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
> >
> > The strange thing is, that if there is no record from T2, then the T1
> > master row is omitted from the result set as well.
> > Example:
> > If T1 has only one record, and T2 is empty, then the query returns
> no rows.
> > In this case I would like to see the record from T1 too!
>
> Move your WHERE condition into the ON clause:
>
> LEFT OUTER JOIN T2
> ON T1.ID = T2.ID AND T2.TYPE = :WANTEDTYPE
>
> Dmitry
>
>

Hi Friends!

Well, I had a BIG HOLE :-) in my SQL knowledge...
I just didn't know that I can move other conditions to the ON clause too...
Thanx for your prompt help!!!

Best regards, Zsolt Balanyi

P.S. OT: If anybody visiting Hungary soon, drop by, tourism in my area
is developing rapidly, and my family has a nice little vineyard, you are
welcome!!!