CODEWITHSUNDEEP
mysqlsqlwordpressmetadatameta-key
dcolumbus
dcolumbus

Reputation: 9714

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?

Upvotes: 2

Views: 3038

Answers (2)

Hobo
Hobo

Reputation: 7611

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.

Upvotes: 5

dcolumbus
dcolumbus

Reputation: 9714

I believe this will work for what I'm looking for ... unless someone can point out where this query fails:

SELECT p.ID
FROM FxFfekP0_posts p
LEFT JOIN FxFfekP0_postmeta pm ON pm.post_id = p.ID 
AND pm.meta_key = 'tag'
WHERE p.post_type = 'pets'
AND pm.meta_key IS NULL 
OR pm.meta_value = ''

Upvotes: 0

Related Questions