Myles, not knowing what you expect to get, it is extremely hard to tell
how to get the 'correct result' ;o). One guess, is that since you do not
use aliases, Firebird may guess that when you try to refer to RECIPIENT
in your subselect, then Firebird thinks of it as referring to RECIPIENT
from your main JOIN CLAUSE. I'm pretty certain this ambiguous query will
not even parse in Firebird 2.1 (and probably not even on Firebird 2.0),
so I guess that you're still using Firebird 1.5? Another thing that may
be different from what you intend, is that the result set will contain
multiple identical rows when one EMAIL_ADDRESS has multiple occurences
in RECIPIENT, that is simple to avoid by using EXISTS rather than INNER
JOIN (you still get duplicates if TEMP_EMAIL3 contains duplicates, but
not if duplicates are only in the RECIPIENT table). Here is an almost
identical query (NULLs may be different) that hopefully can help you on
your way to get the result you want:

SELECT TE.EMAIL_ADDRESS
FROM TEMP_EMAIL3 TE
LEFT JOIN RECIPIENT R2 ON TE.EMAIL_ADDRESS = R2.EMAIL_ADDRESS
AND R2.FK_CLIENT_ID = :A_CLIENT_ID
WHERE R2.EMAIL_ADDRESS IS NULL
AND EXISTS(SELECT * FROM RECIPIENT R
WHERE TE.EMAIL_ADDRESS = R.EMAIL_ADDRESS)

HTH,
Set

Myles Wakeham wrote:

> I'm not sure if my syntax for this is correct for Firebird, but I'm
> certainly not getting the results I expect to get...
>
> SELECT
> TEMP_EMAIL3.EMAIL_ADDRESS
> FROM TEMP_EMAIL3
> INNER JOIN RECIPIENT ON (TEMP_EMAIL3.EMAIL_ADDRESS =
> RECIPIENT.EMAIL_ADDRESS)
> WHERE
> (
> (TEMP_EMAIL3.EMAIL_ADDRESS NOT IN
> (
> SELECT RECIPIENT.EMAIL_ADDRESS
> FROM RECIPIENT WHERE
> RECIPIENT.FK_CLIENT_ID = :A_CLIENT_ID
> )
> )
> )
>
> Myles
>
> ===============================
> Myles Wakeham
> Director of Engineering
> Tech Solutions USA, Inc.
> Scottsdale, Arizona USA
> www.techsolusa.com
> Phone +1-480-451-7440