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?

Best regards, Zsolt Balanyi