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

> Strange thing is, if I run the following script
>
> select fetchedhrs.forkliftcell
> from fetchedhrs
> where EXISTS
> (select * from forklift)
>
> records not actually in the forklift table, but in the fetchedhrs
> table, are returned as part of the result dataset

No, this bit isn't strange. You're simply asking for all records in
fetchedhrs if there exist any record in forklift, regardless of any
values that may be in the forklift table. I think you wanted to try

select fetchedhrs.forkliftcell
from fetchedhrs
where exists
(select * from forklift
where forklift.forkliftcell = fetchedhrs.forkliftcell)

Now, I do not know why your original query

>select fetchedhrs.forkliftcell
>from fetchedhrs
>where fetchedhrs.forkliftcell not in
>(select forklift.forkliftcell from forklift)

didn't work. As Dmitry said, the NOT IN query will not return records
if fetchhrs.forkliftcell is NULL (if you don't know the value, you
don't know whether it is in the subselect or not, and hence that row
does not match the criteria). Using NOT EXISTS you're checking whether
a row exists and if it doesn't, well then the NOT EXISTS have been
fulfilled and the row will be returned.

Presuming fetchedhrs.forkliftcell is not null, I have no idea why your
query doesn't return records. What are your table definitions (i.e.
what kind of field is forkliftcell and if CHAR or VARCHAR, what
character set and collation do you use and what value does not return
as expected) and have you checked that your database is not corrupt
(unlikely, but your problem is also strange)?

HTH,
Set