mysqlsqlwordpressmetadatameta-key

MySQL: return posts if meta_key does NOT exist

I have a query that returns a pet post if the meta_key exists and is not '':

-- Pets that are assigned tags
SELECT p.ID
FROM FxFfekP0_posts p LEFT JOIN FxFfekP0_postmeta pm
ON p.ID = pm.post_id
WHERE p.post_type = 'pets'
AND pm.meta_key = 'tag'
AND pm.meta_value != ''

But now I need to get a result set that contains posts that either do not contain the meta_key tag at all, or the meta_key tag = '' ...

Help?

Solution

  • Something like this should work:

    SELECT p.ID
FROM FxFfekP0_posts p LEFT JOIN FxFfekP0_postmeta pm
ON p.ID = pm.post_id AND pm.meta_key = 'tag'
WHERE p.post_type = 'pets'
AND ifnull(pm.meta_value, '') = ''

    I've moved the pm.meta_key = 'tag' into the join condition, so missing rows still appear in the result set (as NULLs); the ifnull() then allows a single condition to pick up both NULLs and empty strings.