1

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?

dcolumbus
  • 9,596
  • 26
  • 100
  • 165

2 Answers2

5

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.

Hobo
  • 7,536
  • 5
  • 40
  • 50
  • If I've explained moving the condition into the join badly, just compare `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'` with `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 = 'post'`. The latter should return a lot more rows, importantly including those posts with no tag entries in the postmeta table – Hobo Sep 03 '16 at 04:40
  • I basically need to separate the two. I need to know which Pets have a Tag (where tag exists and is not blank or NULL) ... and then know which Pets don't have a tag (no value at all), or the tag entry is blank. – dcolumbus Sep 06 '16 at 19:50
  • I'm a little lost. You have the first query (you posted it in the question); this query returns the second result set (which is what you specifically asked for) - posts whose tags are missing or blank. Unless it didn't work for you because I had the post type wrong from my testing (now corrected)? – Hobo Sep 07 '16 at 01:03
  • Yeah, it didn't initially work. But your updated query seems to be working now. I wish I could fully understand what you've done there... I've never seen the `ifnull()` before. – dcolumbus Sep 07 '16 at 16:46
0

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 = ''
dcolumbus
  • 9,596
  • 26
  • 100
  • 165
  • I'd be a little careful with the precedence of `AND` and `OR`. While it may be unlikely in your particular case, if you have a difference post type with a "tag" meta key, you could get unexpected results. MySQL will interpret your `where` clause as `WHERE ( p.post_type = 'pets' AND pm.meta_key IS NULL ) OR ( pm.meta_value = '' )`. I'd put parentheses around `pm.meta_key IS NULL OR pm.meta_value = ''`. Other than that, isn't this logically the same as my answer? – Hobo Sep 07 '16 at 01:15
  • http://stackoverflow.com/questions/12345569/mysql-or-and-precedence for more detail – Hobo Sep 07 '16 at 01:16