Nobody knows how make this with join???

[alexhaifa]

Hello Guys,
I really need your help,

I have two tables

FIRST TABLE: CADPAL
Fields:

CP_COD (number)
CP_DESCRIPTION (TEXTO 50)

SECOND TABLE: CTRPL

CTP_CODPAL
CTP_CODUSO
CTP_LANGUAGE

I need to retrieve all the records from CADPAL that aren't in the CTRPL, I did something like that:

SELECT CP_CODIGO, CP_DESCRICPTION FROM CADPAL
WHERE CP_CODIGO NOT IN (SELECT CTP_CODPAL FROM CTRPL
WHERE CTP_CODUSO=1 AND CTP_LANGUAGE='US')

The problem is that it works very fine in access, but when I use this SELECT in my application, it gets very slow.

Is there any way to do that with join? I tried but it didn't work

The database is available to download

Thanks
Alexandre

[andi_g69]

What about this:

SELECT CP_CODIGO, CP_DESCRICPTION
FROM CADPAL
LEFT JOIN CTRPL
ON (CP_CODIGO = CTP_CODPAL AND CTP_CODUSO=1)
WHERE CTP_CODPAL IS NULL

[alexhaifa]

You really helped