Svein, is there a better way to structure this query? Your suggestion does
not return any records either.







_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: 23 November 2005 01:13 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: SQL NOT IN

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





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

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

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





SPONSORED LINKS


Technical
<http://groups.yahoo.com/gads?t=ms&k=Technical+support&w1=Technical+support&
w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq
+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technic
al+support&c=6&s=196&.sig=-XIO8GxY6hqd3NaD5WSEyw> support

Computer
<http://groups.yahoo.com/gads?t=ms&k=Computer+technical+support&w1=Technical
+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&
w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsof
t+technical+support&c=6&s=196&.sig=B29J78SYXnNTjjMFBMznqA> technical
support

Compaq
<http://groups.yahoo.com/gads?t=ms&k=Compaq+computer+technical+support&w1=Te
chnical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+s
upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=M
icrosoft+technical+support&c=6&s=196&.sig=7_je1A94xs82CFXUjEqA6g> computer
technical support


Compaq
<http://groups.yahoo.com/gads?t=ms&k=Compaq+technical+support&w1=Technical+s
upport&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4
=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+
technical+support&c=6&s=196&.sig=2zMAuRCo5cJrVBr1Bxa3_w> technical support

Hewlett
<http://groups.yahoo.com/gads?t=ms&k=Hewlett+packard+technical+support&w1=Te
chnical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+s
upport&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=M
icrosoft+technical+support&c=6&s=196&.sig=_ytYU7aXb57AVaeUfmvLcA> packard
technical support

Microsoft
<http://groups.yahoo.com/gads?t=ms&k=Microsoft+technical+support&w1=Technica
l+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support
&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microso
ft+technical+support&c=6&s=196&.sig=4hRo6NXYavRAbTkaYec5Lw> technical
support



_____

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]