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.




-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: 23 November 2005 11:04 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: SQL NOT IN

--- 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






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

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