--- In firebird-support@yahoogroups.com, "emb_blaster"
<EMB_Blaster@...> wrote:

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

Thank you for the input. Basically I'm taking a table of email
addresses to be added to a list of recipients, and my goal is that the
query should only add email addresses that don't already exist for the
nominated client (A_CLIENT_ID) in the RECIPIENTS table already
(basically avoiding duplicate entries).

Hope that explains it a bit better.

Myles