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

>
> --- In firebird-support@yahoogroups.com, "Ann W. Harrison"
> >
> > Conditions that apply to the right hand tables in left joins are
> > better left in the JOIN ON clause.
>
> I want to use parameters and AFAIK parameters can't be used in JOIN
> clause.

No, they can also be used in the join condition under Firebird 1.5 at
least.

>
>
> > If you feel you really must
> > put them in the WHERE clause, you must allow null values for the
> > referenced fields.
>
> I think I do!
> See my original query, there I have
>
> LEFT JOIN TAB_Graafik ON(TAB_Graafik.Tooleping = TAB_Tooleping.UID)
>
> and in the WHERE I have condition
>
>

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

> IS NULL))
>
> but this query doesn't return record from TAB_Tooleping which don't
> have record in TAB_Graafik in given date range. But date range is ORed
> with IS NULL condition, so it should!

I am not interested in your alternative with the join, that will
obviously work. You need to discover which part of your where clause
is bumping out the interesting record.

In your original query, you have three bits AND'd together in your
where clause. (Make sure your brackets are where you expect them to be
if you didn't realise that).

Start by removing the where clause and running the query, you should
see that record. Then add the first of the 3 AND'd clauses. Do you
still see the record? If not, investigate it. If so, add the second,
repeat process.

Adam