--- In firebird-support@yahoogroups.com, "bill_zwirs" <bzwirs@z...>
wrote:

>
> Would appreciate help with an SQL query (Firebird database using
> Delphi 5 Pro and IBX components).
>
> The two tables I am querying are Contractors and Cont_Jobs and the
> result that I want is a list of contractors who would be available
> between the two passed parameters 'jobstart' and 'jobend' (ie.
> those that are not already allocated a job in the period between
> 'jobstart' and 'jobend'). ContractorID is primary key in
> Contractors table and foreign key in Cont_Jobs table. JobStart is a
> TimeStamp field in the Cont_Jobs table - so the parameters I am
> passing are DateTime.
>
> select C.Firstname || ' ' || C.Lastname as Contractor,

C.ContractorID

> from Contractors C left join Cont_Jobs J on C.ContractorID =
> J.ContractorID
> where J.JobStart not between :jobstart and :jobend
>

Try:
SELECT C.Firstname || ' ' || C.Lastname Contractor, C.ContractorID
FROM Contractors C
WHERE C.ContracterId NOT IN
(
SELECT J.ContractorID
FROM Cont_Jobs J
WHERE J.JobStart NOT BETWEEN :JobStart AND :JobEnd
)

Or to be sure there is no overlap in the job periode at all, change
the last WHERE clause to:
WHERE (:JobStart NOT BETWEEN J.JobStart AND J.JobEnd) AND
(:JobEnd NOT BETWEEN J.JobStart AND J.JobEnd)
(not sure parameters are allowed in this position, just try)

Robert.