selecting rows using the enum field in mysql

Question

I am facing an abnormal issue with the enum field(UserStatus) of my table(userinfo). The table structure is as follows:

Field       Type        Default
------------------------------------------
Id          int(11)       (NULL)
FirstName   varchar(50)   (NULL)
LastName    varchar(50)   (NULL)
....        ...           ...
....        ...           ...   
UserStatus  enum('0','1') (NULL)

Here I have rows with the UserStatus values as NULL, 0 and 1. But When I am trying to select rows on the basis of the UserStatus field like:

SELECT 
    * 
FROM 
    userinfo 
WHERE 
    UserStatus<>'1'

Its not working and resulting in an empty resultset.

Thanks in advance.

Remember that NULL <> '1' is NULL and therefore falsy! Have you tried UserStatus <> 1 OR UserStatus IS NULL? — cdhowie
– cdhowie, Commented Apr 10, 2013 at 20:42
@cdhowie : Thanks man, it worked for the UserStatus<>'1' by using the condition NotMine <> '1' OR NotMine IS NULL — Pushpendra
– Pushpendra, Commented Apr 10, 2013 at 20:53
@Pushpendra Glad it worked. I've converted my comment into an answer. — cdhowie
– cdhowie, Commented Apr 10, 2013 at 20:55

cdhowie · Accepted Answer · 2013-04-10 20:54:55Z

3

Remember that NULL <> '1' is itself NULL and therefore your condition won't match rows whose UserStatus is NULL. Use this expression instead:

UserStatus IS NULL OR UserStatus <> '1'

answered Apr 10, 2013 at 20:54

cdhowie

172k25 gold badges303 silver badges324 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

agim · Accepted Answer · 2013-04-10 20:55:21Z

0

It would be a better structure, if you would give a default value and make the field NOT NULL but in this case you could use

 SELECT * 
 FROM userinfo 
 WHERE ifnull(UserStatus, '0') <> '1'

answered Apr 10, 2013 at 20:55

agim

1,84113 silver badges19 bronze badges

Collectives™ on Stack Overflow

selecting rows using the enum field in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related