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



_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Alexandre Benson
Smith
Sent: 22 November 2005 04:22 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] SQL NOT IN



Dion wrote:

>select fetchedhrs.forkliftcell
>
> from fetchedhrs
>
> where fetchedhrs.forkliftcell not in
>
> (select forklift.forkliftcell from forklift)
>
>
>
>Hi,
>
>
>
> 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?
>
>
>
>Thanks,
>
>Dion.
>
>
>

Hi Dion,

The above query lookslike ok !

try this one to check

select
A.forkliftcell
from
fetchedhrs A left join
forklift B on (B.forkliftcell = A.forkliftcell)
where
B.forkliftcell is null

if it returns an empty result set you have a full relation of the too tables
(i.e. no
fetchedhrs without forklift)


see you !


--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br



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

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



* Visit your group "firebird-support
<http://groups.yahoo.com/group/firebird-support> " on the web.


* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>


* Your use of Yahoo! Groups is subject to the Yahoo!
<http://docs.yahoo.com/info/terms/> Terms of Service.



_____



[Non-text portions of this message have been removed]