CODEWITHSUNDEEP
mysqlnull
Theodore
Theodore

Reputation: 35

MYSQL is not null, not skipping results that are null

I thought I had this query built correctly to skip results that are NULL yet the NULL rows still show:

SELECT post_id, 
       MAX(CASE WHEN meta_key = '_payment_meta' 
                THEN meta_value 
                END) as 'DeliveryDate' 
FROM postmeta 
WHERE 'DeliveryDate' IS NOT NULL 
GROUP BY post_id

What is being missed where it is still showing the NULL results?enter image description here

Upvotes: 1

Views: 51

Answers (2)

Akina
Akina

Reputation: 42632

  1. Single quote chars ' are used to quote string literals, to quote column name you must use backtick char. WHERE 'DeliveryDate' IS NOT NULL checks does the string literal 'DeliveryDate' is not null which is always TRUE - i.e. this condition do nothing.
  2. Output column name cannot be used in WHERE - it not exists when WHERE clause is executed (formally, of course). If you need a condition which uses output column you must place it to HAVING clause.

So:

SELECT post_id, 
       MAX(CASE WHEN meta_key = '_payment_meta' 
                THEN meta_value 
                END) as `DeliveryDate`
FROM postmeta 
GROUP BY post_id
HAVING `DeliveryDate` IS NOT NULL

From the other side - you need post_id with meta_key = '_payment_meta' - so you may filter rows before grouping and do simple

SELECT post_id,
       MAX(meta_value) as `DeliveryDate`
FROM postmeta 
WHERE meta_key = '_payment_meta' 
GROUP BY post_id

This query will do the same.

If (post_id, meta_key, meta_value) is defined as UNIQUE (which is logical if the table stores only actual data, not values history) then the query may be simplified to

SELECT post_id,
       meta_value as `DeliveryDate`
FROM postmeta 
WHERE meta_key = '_payment_meta'

Upvotes: 2

ComplexityAverse
ComplexityAverse

Reputation: 104

'DeliveryDate' in the WHERE clause is being interpreted as a string literal ... and as such IS NOT NULL

Upvotes: 1

Related Questions