Hi Myles,

I´m not sure what you expect (can you explain it? or what are wrong
with results?)
In any way let see if alias can help... also change the "Not in
(Select...)" to Not exists.

SELECT
TE1.EMAIL_ADDRESS
FROM TEMP_EMAIL3 TE1
INNER JOIN RECIPIENT RP1 ON (TE1.EMAIL_ADDRESS = RP1.EMAIL_ADDRESS)
WHERE Not Exists(
SELECT RP2.EMAIL_ADDRESS
FROM RECIPIENT RP2 WHERE
(RP2.FK_CLIENT_ID = :A_CLIENT_ID) AND
(RP2.EMAIL_ADDRESS = TE1.EMAIL_ADDRESS)
)

also could you try

SELECT
TE1.EMAIL_ADDRESS
FROM TEMP_EMAIL3 TE1
INNER JOIN RECIPIENT RP1 ON (TE1.EMAIL_ADDRESS = RP1.EMAIL_ADDRESS)
WHERE (RP1.FK_CLIENT_ID <> :A_CLIENT_ID)

well, try it and reply back. please? .. :o)


--- In firebird-support@yahoogroups.com, "Myles Wakeham" <myles@...>
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
>