mysql error IS NOT proper use?

Question

Hello I am bit of a newb when it comes to mysql I running a query and I am getting the following error, to my knowledge the query syntax is correct (obvioulsy not though) this is the error that I am getting,

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your  
 MySQL server version for the right syntax to use near 'FAIL' AND `mc_subscriber.email` =  
 `mc_user.email`' at line 1

my mysql looks like this,

SELECT `mc_subscriber.email`, `mc_user.firstname`, `mc_user.lastname` FROM `subscriber`, `user` WHERE `mc_subscriber.status` = 'SUBSCRIBED` AND `mc_subscriber.status_mx` IS NOT = 'FAIL' AND `mc_subscriber.email` = `mc_user.email`;

I think I may be using IS NOT wrong, I trying to add a condtion that looks for all row where the mx_status is not equal to fail.

Answer 1

It should be: WHERE NOT column = value

so : NOT mc_subscriber.status_mx = 'FAIL'

edit: That works in PostgreSQL at least

edit2:

I wouldn't use that syntax in your case, instead I'd go with mc_subscriber.status_mx != 'FAIL' and use the "NOT column"-construct on boolean columns only. (It might be <> instead of != in MySQLs case don't know for sure)

Answer 2

SELECT `mc_subscriber.email`, `mc_user.firstname`, `mc_user.lastname` FROM `subscriber` WHERE `mc_subscriber.status` = `SUBSCRIBED` AND `mc_subscriber.status_mx` IS NOT = `FAIL` AND `mc_subscriber.email` = `mc_user.email`;

Give that ago.

Looks like you was wrapping with he incorrect quotes, you only really need to use qoutes where "sending" as string such as

SELECT * FROM table WHERE somecolumn = '<Some String>' and someint = 1;

the Qoutes '' are not really needed in columns

Also if your having issues with the IS NOT = 'FAIL' section try changing it to UPPER(mc_subscriber.status_mx) != 'FAIL'

Peace and much love :)

Answer 3

Tables called subscriber and user Table labels of mc_subscriber and mc_user Am I just being dense, or is there some sophisticated MySQL mechanism that autocorrelates the subscriber table with the mc_subscriber label?

And I thought if you used backticks in `mc_subscriber.email`, then both table and column should be individually backticked as `mc_subscriber`.`email`