Problem with inner - left join - I need some clue

[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


A friend Answered with this example

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

I tried and everything went fine with access 2003, but it doesn't worked with my version access 2000. I tried several
other ways, but nothing.

Someone could help me

Thanks

[Rawhide]

It should work with Access 2000. The query is correct. You did leave out the CTP_LANGUAGE='US'.

[alexhaifa]

Hello Friend
Unfortunately I didn't work in my access 2000. Here in the company it worked very fine, the version here is access 2003. Strange, no? If you can send me your email so that we can talk faster.

What about leaving the CTP_LANGUAGE='US', I can't, because I need it to make the filter too.

Thanks in advance for you help

[Rawhide]

Sorry, I don't do direct email support.

I think what may be happening is that the inclusion of "CTP_CODUSO=1" is forcing it into an inner join. It can't have a value in CTP_CODUSO and CTP_CODPAL be null both.

[alexhaifa]

Ok, I am gonna try today. Thanks once again