Hello,

I have created an example:

SET SQL DIALECT 3;

CREATE TABLE T_MAIN (

    TNR   VARCHAR(16) NOT NULL,

    MINB  INTEGER

);

ALTER TABLE T_MAIN ADD CONSTRAINT PK_T_MAIN PRIMARY KEY (TNR);

SET SQL DIALECT 3;

CREATE TABLE T_STOCK (

    TNR     VARCHAR(16),

    AMOUNT  INTEGER

);

ALTER TABLE T_STOCK ADD CONSTRAINT FK_T_STOCK_1 FOREIGN KEY (TNR) REFERENCES T_MAIN (TNR) ON DELETE CASCADE ON UPDATE CASCADE;

INSERT INTO T_MAIN (TNR, MINB)

            VALUES ('AAA', 0);

INSERT INTO T_MAIN (TNR, MINB)

            VALUES ('BBB', 10);

INSERT INTO T_MAIN (TNR, MINB)

            VALUES ('CCC', 10);

INSERT INTO T_MAIN (TNR, MINB)

            VALUES ('DDD', 10);

COMMIT WORK;

INSERT INTO T_STOCK (TNR, AMOUNT)

             VALUES ('AAA', 100);

INSERT INTO T_STOCK (TNR, AMOUNT)

             VALUES ('BBB', 5);

INSERT INTO T_STOCK (TNR, AMOUNT)

             VALUES ('CCC', 15);

COMMIT WORK;

SELECT

a.tnr, a.minb, coalesce(b.amount,0) as m

FROM

t_main a

left join t_stock b on (a.tnr = b.tnr)

WHERE

((a.minb > coalesce(b.amount,0)))

ORDER BY

a.tnr

_________________________________-

I get ccc too. Why? Allthough I have a stock from 15.

If I delete the foreign key from table t_stock, CCC does not display! But with this foreign key it does not work, I get CCC but CCC has a stock greater the minimum amount (minb) and should not be displayed (like in firebird 2.5, 2.1, 2.0)

Thanks