I tried your suggestion, Dmitry, but still get an empty result dataset(as
was the case with my original query. When I ran Alexandres query, I got the
desired result. Should I install v2?



-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Dmitry Yemanov
Sent: 23 November 2005 08:03 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] SQL NOT IN

"Dion" <27824912424@...> wrote:

>
> select fetchedhrs.forkliftcell
> from fetchedhrs
> where fetchedhrs.forkliftcell not in
> (select forklift.forkliftcell from forklift)
>
> I want all forklifts for which I have records in the FetchedHrs table but
> no corresponding record in the Forklift table. I thought that the above

sql

> would work, but I get an empty resultant dataset. I am certain that there

is

> at least one record in the FetchedHrs table which is not in the Forklift
> table.
>
> Is there an issue with this in FB v1.5.2.4731?

Do you have an index on "forklift.forkliftcell"? If so, there's a known bug
in all IB/FB versions. Try this to make sure you're affected:

select fetchedhrs.forkliftcell
from fetchedhrs
where fetchedhrs.forkliftcell not in
(select forklift.forkliftcell || '' from forklift)

and compare the results with your original query.

P.S. This bug is fixed in v2.0.


Dmitry




++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links