JROB JROB - 6 months ago 26
SQL Question

MySQL Select all where DATETIME field is not NULL

I am having trouble selecting the proper values from this query:

SELECT * FROM discounts
WHERE DiscLimit > 0 AND DiscLimit >= DiscRedeemed
OR DiscExp IS NOT NULL AND DiscExp <= NOW()


DiscExp
is a
DATETIME
field that defaults to
NULL
.

I am trying to select current unexpired discounts from the database:

SELECT * FROM discounts WHERE
:


  1. The limit is > 0 (a limit exists) AND the limit is >= the number
    redeemed

  2. An expiration exists (not null) and the expiration is in
    the future



It seems to be retrieving values where the
DATETIME
field is
NULL
. Any help would be much appreciated.

Answer

You need parentheses! Try this:

SELECT * FROM discounts 
WHERE (DiscLimit > 0 AND DiscLimit >= DiscRedeemed) 
OR (DiscExp IS NOT NULL AND DiscExp <= NOW())

But, from your question, I'm not sure if the OR is correct. If you want it to meet all of the following conditions:

  • The limit is > 0 (a limit exists)
  • The limit is >= the number redeemed
  • An expiration exists (not null)
  • The expiration is in the future

You want, this:

SELECT * FROM discounts 
WHERE DiscLimit > 0 AND DiscLimit >= DiscRedeemed
AND DiscExp IS NOT NULL AND DiscExp <= NOW()

Or, if you want it to meet all of the following conditions:

  • The limit is > 0 (a limit exists)
  • The limit is >= the number redeemed
  • The expiration is in the future or does not exist (IS NULL)

You want this:

SELECT * FROM discounts 
WHERE DiscLimit > 0 AND DiscLimit >= DiscRedeemed
AND (DiscExp IS NULL OR DiscExp <= NOW())

Note that you don't actually need to use DiscExp IS NOT NULL AND DiscExp <= NOW(), because NULL does not evaluate to being <= anything.

Hope this helps!