Sorry, I didn't notice the last few replies to this thread where it is
better described what you want (my reply was directed towards your
initial email only, where it still wasn't evident what you were looking
for). Emb_blaster has already written an appropriate query, and Mark
almost got it right as well, the only thing missing is that the
parameter has to be in the JOIN clause:

SELECT te.email_address
FROM temp_email3 te
LEFT JOIN recipient re ON te.email_address = re.email_address
AND re.fk_client_id = :A_CLIENT_ID
WHERE re.email_address IS NULL

Basically, you have two identically good solutions to choose from.

Set

Svein Erling Tysvaer wrote:

> 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