CODEWITHSUNDEEP
mysql
reggie
reggie

Reputation: 3674

Query rows for which meta data key does not exist

SELECT DISTINCT wposts.ID AS ID
FROM `wp_posts` AS wposts
JOIN `wp_postmeta` AS postmeta ON (wposts.ID = postmeta.post_id)
WHERE wposts.post_type = 'post'
AND wposts.ID NOT IN (
    SELECT wp_posts.ID FROM wp_posts, `wp_postmeta` AS postmeta2
    WHERE wp_posts.ID = postmeta2.post_id
    AND postmeta2.meta_key = 'z_latitude'
)
ORDER BY wposts.post_date DESC

The query is a mess. I simply want to query all rows that do not have a certain meta key. Above, I query all rows that have the meta key and then exclude them from the outer query. I don't know how to write this better.

Upvotes: 1

Views: 1044

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115660

SELECT wposts.ID AS ID
FROM doxy_posts AS wposts
  LEFT JOIN doxy_postmeta AS postmeta
    ON wposts.ID = postmeta.post_id
    AND postmeta.meta_key = 'z_latitude'
WHERE wposts.post_type = 'post' 
  AND postmeta.post_id IS NULL
ORDER BY wposts.post_date DESC

or:

SELECT wposts.ID AS ID
FROM doxy_posts AS wposts
WHERE wposts.post_type = 'post' 
  AND NOT EXISTS
        ( SELECT * 
          FROM doxy_postmeta AS postmeta
          WHERE wposts.ID = postmeta.post_id
            AND postmeta.meta_key = 'z_latitude'
        ) 
ORDER BY wposts.post_date DESC

Upvotes: 0

Bohemian
Bohemian

Reputation: 425448

Use an outer join via a left join and grab rows that didn't join:

SELECT DISTINCT wposts.ID AS ID
FROM `doxy_posts` AS wposts
left JOIN `doxy_postmeta` AS postmeta ON wposts.ID = postmeta.post_id
WHERE postmeta.post_id is null
ORDER BY wposts.post_date DESC

Upvotes: 2

Related Questions