""Luiz Alves"" <cprmlao@...> wrote in message
news:000e01c197db$fed0bfd0$010aa8c0@......

> I have the next querys with FB RC2:
>
> Q3:
> select A.indice,A.nserv,B.indice as Indice1,B.nserv as nserv1
> from servico A
> left outer join servico B on A.nserv=B.nserv
> where A.ident_cp=13 and B.ident_cp=395
>
> Result:
> Indice nserv Indice1 nserv1
> -----------------------------------
> 5 1 1874 1
> 6 2 1875 2
> 7 3 1876 3
> 8 4 1877 4
>
> With this left join the correct result don´t will be:
> Result:
> Indice nserv Indice1 nserv1
> -----------------------------------
> 5 1 1874 1
> 6 2 1875 2
> 7 3 1876 3
> 8 4 1877 4
> 9 5 null null

Which bug, please? I think you want to write instead:

select A.indice,A.nserv,B.indice as Indice1,B.nserv as nserv1
from servico A
left outer join servico B on A.nserv=B.nserv
and B.ident_cp=395
where A.ident_cp=13

or this:

select A.indice,A.nserv,B.indice as Indice1,B.nserv as nserv1
from servico A
left outer join servico B on A.nserv=B.nserv
and A.ident_cp=13 and B.ident_cp=395

Is this your problem? I think it's a logical bug in your SQL statement, not
in the engine. Given the left join, both statements above should give the
same results. As I think those issues, the steps for me are:
the inner/outer JOIN condition is applied
the WHERE condition is applied
the HAVING condition is applied
the ORDER BY proceeds

The WHERE clause has the same effect than the ON clause applied to the
mandatory table. This is why ON and WHERE produce same effects with an INNER
JOIN (both tables being mandatory) but different effects with an OUTER JOIN
(left, right or no mandatory table).

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing