0

What I'm really trying to do is identify all WordPress post IDs (from wp_posts) where a Yoast SEO focus keyword record does not exist in wp_postmeta. This is a simplified version of the problem:

enter image description here

Want to return T1.id values 2,6 -- ie: only the values of T1.id that are equal to T2.fid, where T1.a = "2" and where T2.b does not equal "3".

ATTEMPT:

SELECT DISTINCT id FROM `T1` INNER JOIN `T2` ON `id` = `fid` 
WHERE `a` = "2" AND NOT `b` = "3" ORDER BY ID ASC

This doesn't work. The same values are returned whether the "AND NOT 'b' = "3" is included or not. I am trying to get the 'b' = "3" condition to eliminate the id from the returned set.

Any help?

Thx!

FeralReason
  • 382
  • 2
  • 10

1 Answers1

0

Alright - I think I figured this out. So first, the answer to the question as posted is:

SELECT DISTINCT `id` FROM `T1` INNER JOIN `T2`
ON `id` = `fid`
WHERE `a` = "2" 
AND `fid` NOT IN (SELECT `fid` FROM `T2` WHERE `b` = "3")
ORDER BY `id` ASC

And for you WordPress SEOs, my actual goal was get the URLS for all published posts that had NO Yoast SEO Keywords added (the posts that need work). This seems to do the trick:

SELECT DISTINCT `ID`, CONCAT("http://mysite/",`post_name`) , `post_type`,  `post_status`
FROM `wp_posts` INNER JOIN `wp_postmeta` ON `post_id` = `ID`
WHERE `post_type` = "post"  AND `post_status` = "publish"
AND `post_id` NOT IN
(SELECT `post_id` FROM `wp_postmeta` WHERE `meta_key` = "_yoast_wpseo_focuskw")
ORDER BY `ID` ASC

NOTE: The URL assumes that your permalink settings are based on "Post name".

If you want to check a "page" or custom post type, simply set post_type to the appropriate value.

FeralReason
  • 382
  • 2
  • 10