--- In firebird-support@yahoogroups.com, "Dion" wrote:

> I do not understand why using NOT EXISTS does not work in the
> following case
>
> select fetchedhrs.forkliftcell
> from fetchedhrs
> where exists
> (select forklift.forkliftcell
> from forklift
> join forkliftcontract on
> (forkliftcontract.forkliftoid = forklift.oid)
> join contract on (contract.oid = forkliftcontract.contractoid)
> join periods on (periods.oid = 5891)
> where ((contract.fromdate <= periods.start) and
> (contract.todate >= periods.ENDdate)) or
> ((contract.fromdate <= periods.start) and
> ((contract.todate <= periods.enddate) and
> (contract.todate >= periods.start))) or
> (((contract.fromdate >= periods.start) and
> (contract.fromdate <= periods.enddate)) and
> (contract.todate >= periods.enddate))
> )
>
> This query returns an empty set. When I run the two parts of the
> query separately, I get the following result:-
>
> (the subselect)
> FORKLIFTCELL
> 082345678
> 082567893
> 072345678
> 072987654
> 076298766
> 076234543
> 084287655
> 082987654
> 072988976
> 076213456
> 072987666
>
> When I run the top part of the query(select forklift...), I get the
> following result:-
>
> FORKLIFTCELL
> 074887643
> 082345678
> 082567893
> 072987654
> 076298766
> 076234543
> 084287655
> 082987654
> 072988976
> 076213456
> 07398777777 >>
> 0987 >>
>
> My understanding of NOT EXISTS is that firebird will check the
> records from the first result set and check if each value is in the
> subselect result set. If not, it will return it.
>
> Thanks,
> Dion.

Nope, this is not correct. Firebird will execute the subquery for each
potential row in the main select and if it finds one or more records
the record EXISTS. If not, then it NOT EXISTS. It does not
automagically guess that it should also compare
fetchedhrs.forkliftcell to forklift.forkliftcell - you haven't told
it, so it assumes that this is irrelevant. Change your query to the
below code (note the addition of the last line - I assume that using
EXISTS and not NOT EXISTS was simply a little typo).

HTH,
Set

> select fetchedhrs.forkliftcell
> from fetchedhrs
> where not exists
> (select *
> from forklift
> join forkliftcontract on
> (forkliftcontract.forkliftoid = forklift.oid)
> join contract on (contract.oid = forkliftcontract.contractoid)
> join periods on (periods.oid = 5891)
> where ((contract.fromdate <= periods.start) and
> (contract.todate >= periods.ENDdate)) or
> ((contract.fromdate <= periods.start) and
> ((contract.todate <= periods.enddate) and
> (contract.todate >= periods.start))) or
> (((contract.fromdate >= periods.start) and
> (contract.fromdate <= periods.enddate)) and
> (contract.todate >= periods.enddate)) and
> forklift.forkliftcell = fetchedhrs.forkliftcell
> )