Search does not work correctly when using JOIN

2

I have the SQL below and the parameter where the JOIN contratos co ON c.email != co.email part is not working. My goal is that c.email is not in the contracts table. No SQL error is shown.

If I do so JOIN contratos co ON c.email = co.email works perfectly ... but I want the opposite result.

SELECT os.osID, c.nome, c.email, os.idioma, os.dataAtualizacao
FROM os JOIN cadastroCliente c ON os.idcliente = c.cadastroClienteID AND c.email != '' JOIN contratos co ON c.email != co.email
WHERE os.status = '1' 
AND os.respondido = '1'
AND os.emailAutRecuperar1 = '0'
AND os.dataAtualizacao < CURRENT_DATE()-3 GROUP BY osID

I've tried it in a different way but it's also unsuccessful.

SELECT os.osID, c.nome, c.email, os.idioma, os.dataAtualizacao
FROM os, contratos co JOIN cadastroCliente c ON os.idcliente = c.cadastroClienteID AND c.email != ''
WHERE os.status = '1' 
AND os.respondido = '1'
AND os.emailAutRecuperar1 = '0'
AND os.dataAtualizacao < CURRENT_DATE()-3 AND c.email != co.email GROUP BY osID
    
asked by anonymous 28.09.2015 / 00:42

1 answer

3

Have you tried using NOT IN ?

This command would filter c.Email not in (select co.Email from Contratos co)

WHERE c.Email NOT IN
(
SELECT co.Email from Contratos co
)
    
28.09.2015 / 00:49