MYSQL - Select not working properly

Asked

Viewed 27 times

1

Good night,

I have below the search in the database, but it returns me error or does not work on line 4 where it says "JOIN contracts co ON c.email != co.email", can you do otherwise, or improve the search? Thank you

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
mysql

  • which error appears?

  • In reality it does not give an error, but with the without that part "JOIN contracts co ON c.email != co.email" shows the same number of results...

1 answer

1


This sign: !=, does not exist in the default SQL syntax. There is even an entry in the Mysql manual with this, but the recommended, even for approaching SQL ANSI, is to use <>, common to all SQL interpreters of any relational technology.

It is also important to check whether c.email is not null. Anything compared to null gives false, except is not null.

The right thing would be:

SELECT os.osID, c.nome, c.email, os.idioma, os.dataAtualizacao
    FROM os
JOIN cadastroCliente c ON os.idcliente = c.cadastroClienteID AND c.email <> '' and c.email is not null
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

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.