query with criterium 'where not' 1

[Pampers]

Hi Everyone,
I have to tables, tblClients2006 and tblClients. I want make a query that selects all Clients from tblClients except those who are in tblClients2006

Pampers
Keeping it simple can be complicated

 

[AlexCuse]

you want to use a left join, and filter for only records without a match on the right side. Just replace SomeKey with whatever you can use to compare the two tables:

select a.*
from tblClients a
left join tblClients2006 b
on a.SomeKey = b.SomeKey
where b.SomeKey is null

HOpe it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Pampers]

Tnx AlexCuse,

That works. And the I tried:

SELECT SYSADM_DEBITEUR.ZOEKNAAM, SYSADM_DEBITEUR.CDRELATIE, tblKlanten2006.CDRELATIE
FROM tblKlanten2006 RIGHT JOIN SYSADM_DEBITEUR ON tblKlanten2006.CDRELATIE = SYSADM_DEBITEUR.CDRELATIE
WHERE (((tblKlanten2006.CDRELATIE) Is Null));

Which looks like it is working also...


Pampers
Keeping it simple can be complicated

 

[Pampers]

What is NOT so working today is my head. Thanx AlexCuse, things are coming back to me again...

Pampers
Keeping it simple can be complicated

 

[AlexCuse]

Yup, right and left joins are really the same thing, but just have the tables sitting on different sides of the join. I just always use left, because it keeps things simple (which like you say, can be very complicated ;-) )

I'm glad you got it working!

Alex

Ignorance of certain subjects is a great part of wisdom