Hi Dion,
it took a while to understand what your question was. My guess is that
you really want a LEFT JOIN, but no WHERE clause (normally, you would
only use the right table in the WHERE clause if you are looking for
values not in the right table at all - that does not seem to be what
you are looking for).

Try this:

select forklift.*, forkliftcontract.oid as forkliftcontractoid ,
forkliftcontract.fllevel, forkliftcontract.colorvalue,
forkliftcontract.fromdate, forkliftcontract.todate, forklift.oid as
forkliftoid, Forkliftcontract.fromdate as ContractFromDate,
Forkliftcontract.Todate as ContractToDate
from forklift
left join forkliftcontract on forkliftcontract.forkliftoid =
forklift.oid
and ((forkliftcontract.fromdate <= :startdate and
forkliftcontract.todate >= :Enddate) or
(forkliftcontract.fromdate <= :startdate and
forkliftcontract.todate between :startdate and :enddate) or
(forkliftcontract.fromdate between :startdate and :enddate and
forkliftcontract.todate between :startdate and :enddate) or
(forkliftcontract.fromdate between :startdate and :enddate and
(forkliftcontract.todate >= :enddate))
order by forkliftcontract.contractoid, forkliftcontract.fllevel

I moved everything from the WHERE clause to the JOIN clause, replaced
a few >=..<= with BETWEEN, and removed some parenthesis (moving from
the WHERE to the JOIN clause changes which records the query returns,
the other two just made it easier for me to read).

HTH,
Set

--- In firebird-support@yahoogroups.com, "Dion" wrote:
> Thanks. OK. Each vehicle is associated(when assigned to) with a
> contract. My SQL fetches all records for each vehicle if the period
> fetched fits into the contract period supplied. If not, the vehicle
> is not returned. So, if the record in the vehicleContract table fits
> into the current period I am interested in, it is returned, as well
> as those with no records(null values). Problem is if the vehicle has
> been contracted out to a period not between the supplied period, the
> record simply is not returned. I need this record returned as well.
>
> I could fetch 2 tables, 1 containing all vehicles(from vehicle
> table), and, another containing records which fit the bill, left
> joined. I would like 1 result set returned, with those vehicles not
> having any matches(as null values) as well as those having matches
> in another period. I hope this makes some sense. The following sql
> does not return the records having matches not in the period of
> concern.:-
>
> select forklift.*, forkliftcontract.oid as forkliftcontractoid ,
> forkliftcontract.fllevel, forkliftcontract.colorvalue,
> forkliftcontract.fromdate, forkliftcontract.todate,
> forklift.oid as forkliftoid,
> Forkliftcontract.fromdate as ContractFromDate,
> Forkliftcontract.Todate as ContractToDate
> from forklift
> left join forkliftcontract on
> (forkliftcontract.forkliftoid = forklift.oid)
> where ( ((forkliftcontract.fromdate <= :startdate) and
> (forkliftcontract.todate >= :Enddate)) or
> ((forkliftcontract.fromdate <= :startdate) and
> ((forkliftcontract.todate <= :enddate) and
> (forkliftcontract.todate = :startdate))) or
> ((forkliftcontract.fromdate >= :startdate and
> forkliftcontract.fromdate <= :enddate) and
> (forkliftcontract.todate >= :startdate and
> forkliftcontract.todate <= :enddate)) or
> (((forkliftcontract.fromdate >= :startdate) and
> (forkliftcontract.fromdate <= :enddate)) and
> (forkliftcontract.todate >= :enddate)) )
> or ((forkliftcontract.fromdate is null) and
> (forkliftcontract.todate is null))
> order by forkliftcontract.contractoid, forkliftcontract.fllevel
>
> Excuse the formatting.
>
> Many thanks,
> Dion