--- In firebird-support@yahoogroups.com, "ainpoissee" <ainpoissee@...>
wrote:

>
> Hi,
>
> When I execute following query (FB 1.5)
>
> SELECT DISTINCT TAB_Tooleping.UID FROM TAB_Tooleping
> JOIN TAB_Tootaja ON(TAB_Tootaja.UID = TAB_Tooleping.Tooline)
> LEFT JOIN TAB_Graafik ON(TAB_Graafik.Tooleping = TAB_Tooleping.UID)
> LEFT JOIN TAB_Vahetus ON(TAB_Vahetus.UID = TAB_Graafik.Vahetus)
> WHERE((TAB_Tooleping.Alates<='31.10.2006')AND((TAB_Tooleping.Kuni IS
> NULL)OR(TAB_Tooleping.Kuni>='01.10.2006')))
>
>

AND(((TAB_Graafik.Kuupaev<='31.10.2006')AND(TAB_Graafik.Kuupaev>='01.10.2006'))OR(TAB_Graafik.Kuupaev

> IS NULL))
> AND((TAB_Vahetus.Kestus>480)OR(TAB_Tootaja.Isikukood STARTING
> '3')OR(TAB_Tooleping.Tooline=1))
> ORDER BY TAB_Tootaja.Perenimi ASC
>
>
> record where TAB_Tooleping.Tooline=1 doesn't show up in resultset.
> That record doesn't have any records in TAB_Graafik which match
> condition
>

(TAB_Graafik.Kuupaev<='31.10.2006')AND(TAB_Graafik.Kuupaev>='01.10.2006')

> but because of using LEFT JOIN and "OR(TAB_Graafik.Kuupaev IS NULL)" I
> did expect it to show up... What is right, should the record be in
> resultset and there is a bug in FB or my logic is flawed and FB is

right?

>
> If I move TAB_Graafik.Kuupaev test into JOIN like
>
> SELECT DISTINCT TAB_Tooleping.UID FROM TAB_Tooleping
> JOIN TAB_Tootaja ON(TAB_Tootaja.UID = TAB_Tooleping.Tooline)
> LEFT JOIN TAB_Graafik ON(TAB_Graafik.Tooleping =
>

TAB_Tooleping.UID)AND((TAB_Graafik.Kuupaev<='31.10.2006')AND(TAB_Graafik.Kuupaev>='01.10.2006'))

> LEFT JOIN TAB_Vahetus ON(TAB_Vahetus.UID = TAB_Graafik.Vahetus)
> WHERE ((TAB_Tooleping.Alates<='31.10.2006')AND((TAB_Tooleping.Kuni IS
> NULL)OR(TAB_Tooleping.Kuni>='01.10.2006')))
> AND((TAB_Vahetus.Kestus>480)OR(TAB_Tootaja.Isikukood STARTING
> '3')OR(TAB_Tooleping.Tooline=1))
> ORDER BY TAB_Tootaja.Perenimi ASC
>
> then I do get the answer I expect, but as the date is parameter I
> would like to keep this condition in WHERE.
>
>
> TIA
> ain

Hello Ain,

Looking straight at your query, it seems OK. Perhaps you could try and
identify which of the where clause conditions eliminate the record of
interest.

Adam