At 06:57 PM 6/03/2007, you wrote:

>Helen Borrie wrote:
> >
> > At 05:07 PM 6/03/2007, you wrote:
> > >Hi all!
> > >
> > >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!
> >
> > First, I'm assuming you don't *really* have fields named VALUE and
> > TYPE...or, if you do, then they are double-quoted.
> >
> > Try this:
> > SELECT
> > T1.ID,
> > T1.NAME,
> > COALESCE(SUM(T1."VALUE"), 0) <-------you had an unqualified column ref
> > here
> > FROM T1
> > LEFT OUTER JOIN T2
> > ON T1.ID = T2.ID
> > WHERE
> > T2."TYPE" = :WANTEDTYPE
> > GROUP BY T1.ID, T1.NAME
> >
> > If that doesn't fix it, come back and tell us what version of
> > Firebird you're using.
> >
> > ./heLen
> >
> >
>Hi Helen!
>
>The field names in the example were of course imaginary - the original
>ones are in Hungarian :D:D:D
>Here is a small sample code:
>
>CREATE TABLE LAYERS (
> LAYERID INTEGER NOT NULL,
> LAYERNAME VARCHAR (50) CHARACTER SET WIN1250 NOT NULL COLLATE WIN1250);
>
> LAYERID LAYERNAME
> 1 Lake layer
> 2 Forest layer
> 3 River layer
> 4 Desert layer
>
>CREATE TABLE OBJECTS (
> OBJECTID INTEGER NOT NULL,
> LAYERID INTEGER NOT NULL,
> AREAID INTEGER NOT NULL,
> NAME VARCHAR (50) CHARACTER SET WIN1250 NOT NULL COLLATE WIN1250,
> PRICE FLOAT NOT NULL);
>
> OBJECTID LAYERID
>AREAID NAME PRICE
> 1 1
>1 xxx 10,00
> 2 2
>1 fgfg 10,00
> 3 3 1
>fhjfghd 10,00
> 4 4 1
>jzhtzut 10,00
> 5 1 2
>fghfghfg 10,00
> 6 3 2
>dhdfgdfg 10,00
> 7 4 2
>dfrgdfgd 10,00
> 8 1
>2 fghfg 10,00
> 9 3 2
>dhgdfdgds 10,00
> 10 1 2
>fghjftert 10,00
>
>SELECT LAYERS.LAYERID, LAYERNAME, COALESCE(SUM(PRICE), 0)
>FROM LAYERS LEFT OUTER JOIN OBJECTS ON LAYERS.LAYERID = OBJECTS.LAYERID
>WHERE LAYERS.LAYERID > 0 AND AREAID = 2
>GROUP BY LAYERS.LAYERID, LAYERNAME
>
> LAYERID LAYERNAME COALESCE
> 1 Lake layer 30,00
> 3 River layer 20,00
> 4 Desert layer 10,00
>
>As you can see, there is no result row for LAYERID 2, which I would
>expect to be 0,00...
>Is this a bug, or a feature?

Hmm, I think you missed the point....so let's rewrite *this* query
with the proper identifier syntax (corrections show in lower case):

SELECT
LAYERS.LAYERID,
layers.LAYERNAME,
COALESCE(SUM(layers.PRICE), 0) as total
FROM LAYERS
LEFT OUTER JOIN OBJECTS ON
LAYERS.LAYERID = OBJECTS.LAYERID
WHERE LAYERS.LAYERID > 0 AND objects.AREAID = 2
GROUP BY LAYERS.LAYERID, layers.LAYERNAME

As an alternative to thsi (since you still don't say what version of
Firebird you are using):

SELECT
LAYERS.LAYERID,
layers.LAYERNAME,
COALESCE(SUM(layers.PRICE), 0) as total
FROM LAYERS
LEFT OUTER JOIN OBJECTS ON
LAYERS.LAYERID = OBJECTS.LAYERID
and objects.objects.AREAID = 2
WHERE LAYERS.LAYERID > 0 /* AND */
GROUP BY LAYERS.LAYERID, layers.LAYERNAME

./heLen