SQL query should not return rows that contain null values in pivot table

1 Views

I have created a query using a pivot table to extract some metadata (from my wordpress database) for latitude and longitude values associated with posts. I would like to only return posts that have these particular pieces of metadata, and ignore all other posts, but I am unable to filter out those posts that don’t have values or these meta keys. Here is my SQL: 

SELECT wp_posts.ID,   
wp_posts.post_title, wp_posts.post_modified_gmt,
MAX(CASE WHEN wp_postmeta.meta_key = "_wp_geo_longitude" then wp_postmeta.meta_value ELSE NULL END) as longitude,
MAX(CASE WHEN wp_postmeta.meta_key = "_wp_geo_latitude" then wp_postmeta.meta_value ELSE NULL END) as latitude
FROM wp_posts JOIN wp_postmeta  ON ( wp_postmeta.post_id = wp_posts.ID)  
WHERE wp_posts.post_status = 'publish'                  
GROUP BY wp_posts.ID, wp_posts.post_title;

I have tried adding the following to the WHERE clause with zero effect:

Read More
WHERE longitude IS NOT NULL
WHERE wp_postmeta.meta_value IS NOT NULL

And variations of that, but all posts are still returned, regardless of null values.

Post Views: 1

Related posts

1 comment

  1. Just add a having clause:

    having longitude is not null and latitude is not null

    This is a situation where the join method of pivoting might be more efficient:

    SELECT p.ID, p.post_title, p.post_modified_gmt,
       lng.meta_value as longitude, lat.meta_value as latitude
FROM wp_posts p JOIN
     wp_postmeta lat
     ON wp_postmeta.post_id = p.ID AND lat.meta_key = '_wp_geo_latiitude' JOIN
     wp_postmeta lng
     ON wp_postmeta.post_id = p.ID AND lng.meta_key = '_wp_geo_longitude'
WHERE p.post_status = 'publish';

    In general, I prefer the aggregation method more than the join method because it is more flexible. However, you are only getting two fields and you want to ensure that both are there. That means that the option of using joins is quite reasonable in this case.

Comments are closed.