CODEWITHSUNDEEP
mysql
Apoorva Shah
Apoorva Shah

Reputation: 632

Query gives empty result because of where clause

Table : tbl_email_scheduler
Here date_to_send = '2013-10-12' AND send_status = NULL. (At time createing table i have given send_status default NULL)

Mysql : 

SELECT
  id,
  to_users,
  subject,
  message,
  product_id,
  date_to_send,
  send_status,
  status,
  createdate
FROM tbl_email_scheduler
WHERE date_to_send = '2013-10-12'

Gives me result

But when i 

SELECT
  id,
  to_users,
  subject,
  message,
  product_id,
  date_to_send,
  send_status,
  status,
  createdate
FROM tbl_email_scheduler
WHERE date_to_send = '2013-10-12'
    and send_status != 'Y'

it gives me empty result why? Pls answer.

Upvotes: 0

Views: 344

Answers (4)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

In your second query you are defining if send_status is not Y.
It means if send_status if NULL it will fetch nothing.

You should try it like this

SELECT
  id,
  to_users,
  subject,
  message,
  product_id,
  date_to_send,
  send_status,
  status,
  createdate
FROM tbl_email_scheduler
WHERE date_to_send = '2013-10-12'
    and (send_status != 'Y' AND send_status IS NOT NULL)

This will first fetch all non-null results and apply not equals Y condition

Upvotes: 0

Raghvendra Parashar
Raghvendra Parashar

Reputation: 4053

because database server treats NULL as nothing, try this

SELECT 
  id,
  to_users,
  subject,
  message,
  product_id,
  date_to_send,
  send_status,
  status,
  createdate
FROM tbl_email_scheduler 
WHERE date_to_send = '2013-10-12' and (send_status !='Y' OR send_status IS NULL)

Upvotes: 2

Nikhil N
Nikhil N

Reputation: 4577

Any comparison with NULL yields NULL.You should use following operators according to your needs while dealing with NULL.

x IS NULL - determines whether left hand expression is NULL,

x IS NOT NULL - like above, but the opposite,

x <=> y - compares both operands for equality in a safe manner, i.e. NULL is seen as a normal value.

Upvotes: 0

vijaykumar
vijaykumar

Reputation: 4806

I think your mistake is not equal in mysql '<>'

Try this

SELECT id, to_users, subject, message, product_id, date_to_send, send_status, STATUS , createdate FROM tbl_email_scheduler WHERE date_to_send = '2013-10-12' AND send_status <> 'Y' LIMIT 0 , 30

Upvotes: 0

Related Questions