Hi Karol,

the same A2.ID is linked to multiple records that fulfill both first and
second criteria. This way you DO get 210 matches, but they are only 207
DISTINCT ID-s.

Regards, Zsolt

On Tue, Jun 28, 2011 at 11:25 AM, karolbieniaszewski <
liviuslivius@...> wrote:

> **
>
>
> Hi
>
> i have problem with result of this query simple union
> difference is only in filter in where clause
>
> SELECT 1, COUNT(DISTINCT A2.ID) AS ILE_Z_FLAGA, COALESCE(OSTE2.FLAGA_TEST,
> 1) AS FLAGA_TEST
> FROM
> OBIEKTST_EX OSTE2
> INNER JOIN OBIEKTST_DB OST2 ON OST2.ID=OSTE2.ID_OBIEKTST
> INNER JOIN ADRESY_DB A2 ON A2.ID=OST2.ID_OBJ
> WHERE
> A2.STAN='Aktywny'
> AND OST2.SYSTEM='R0'
> AND (OSTE2.FLAGA_TEST IS NULL OR OSTE2.FLAGA_TEST=1)
> GROUP BY COALESCE(OSTE2.FLAGA_TEST, 1)
>
>
> UNION ALL
>
> SELECT 2, COUNT(DISTINCT A2.ID), COALESCE(OSTE2.FLAGA_TEST, 1) AS
> FLAGA_TEST
> FROM
> OBIEKTST_EX OSTE2
> INNER JOIN OBIEKTST_DB OST2 ON OST2.ID=OSTE2.ID_OBIEKTST
> INNER JOIN ADRESY_DB A2 ON A2.ID=OST2.ID_OBJ
> WHERE
> A2.STAN='Aktywny'
> AND OST2.SYSTEM='R0'
> AND OSTE2.FLAGA_TEST IS NOT NULL AND OSTE2.FLAGA_TEST=0
>
> GROUP BY COALESCE(OSTE2.FLAGA_TEST, 1)
>
> UNION ALL
>
> SELECT 3, COUNT(DISTINCT A2.ID), NULL
> FROM
> OBIEKTST_EX OSTE2
> INNER JOIN OBIEKTST_DB OST2 ON OST2.ID=OSTE2.ID_OBIEKTST
> INNER JOIN ADRESY_DB A2 ON A2.ID=OST2.ID_OBJ
> WHERE
> A2.STAN='Aktywny'
> AND OST2.SYSTEM='R0'
> /*
> GROUP BY COALESCE(OSTE2.FLAGA_TEST, 1)
> */
>
> result is
> 1 199 1
> 2 11 0
> 3 207!!!! [null]
>
> i do not know how third query in union return 207 value not 199+11=210
>
> if i uncoment last line GROUP BY COALESCE(OSTE2.FLAGA_TEST, 1)
>
> result is
> 1 199 1
> 2 11 0
> 3 11 [null]
> 3 199 [null]
>
> and now third query return two rows with 11 and 199=210!!
>
> i do somethink wrong or this is some Firebird problem?
>
> Karol Bieniaszewski
>
>
>

[Non-text portions of this message have been removed]