--- In firebird-support@yahoogroups.com, "vladman992000" <myles@...>
wrote:

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

hi again,

so, if I understand rigth, I´m thinking that the inner join is
working "against" you. The table RECIPIENT cannot be joined in this
way to TEMP_EMAIL.

try it below:

SELECT TE1.EMAIL_ADDRESS
FROM TEMP_EMAIL3 TE1
WHERE Not Exists(
SELECT 1
FROM RECIPIENT RP2 WHERE
(RP2.FK_CLIENT_ID = :A_CLIENT_ID) AND
(RP2.EMAIL_ADDRESS = TE1.EMAIL_ADDRESS)
)