CODEWITHSUNDEEP
mysqlnull
Shackrock
Shackrock

Reputation: 4701

MYSQL Null issues

I've got a simple DB query. Basically I want to select all rows that are NOT equal to a certain set of strings, or is empty (if it's empty I do NOT want it to be selected).

This is what I've got:

select * from tbl_user where secretCode != ('S00' OR 'S05' OR 'A10' OR '')

The datatype of secretcode is CHAR(4), NULL (NO), DEFAULT NONE

What am I doing wrong here, should I be using NULL instead of '' ?

Thanks.

Upvotes: 1

Views: 96

Answers (4)

faq
faq

Reputation: 3076

use:

SELECT * FROM `tbl_users` WHERE `secretCode` <> ('S00' OR 'S05' OR 'A10') AND `secretCode` IS NOT NULL

Upvotes: 0

Milad Naseri
Milad Naseri

Reputation: 4118

You should use the IS NOT NULL operator according to here

SELECT * FROM `tbl_users`
WHERE `secretCode` != ('S00' OR 'S05' OR 'A10') AND `secretCode` IS NOT NULL

Upvotes: 0

Aurelio De Rosa
Aurelio De Rosa

Reputation: 22162

I think your query should be like this:

select * from tbl_user where secretCode NOT IN ('S00', 'S05', 'A10', '') AND
secretCode IS NOT NULL

Upvotes: 2

ChristopheD
ChristopheD

Reputation: 116207

select 
    * 
from 
    tbl_user 
where 
    secretCode not in ('S00', 'S05', 'A10') and secretCode not is null

Checking for NULL shoud be done with is null or not is null.

Upvotes: 1

Related Questions