I have 3 tables wp_posts, wp_postmeta and a custom table postslist and I want to find the posts that are in postslist and are missing from wp_posts. The comparison is made with the postslist.id and a custom field holding this id value in wp_postmeta (“oldpostid”).
I tried making the comparison with two PHP loops but because the records are close to 100.000 would take forever and I’m searching for a more efficient way.
This is what I tried so far but something I’m missing…
SELECT meta2.id, meta2.title, meta2.main_text FROM wp_posts INNER JOIN wp_postmeta meta1 ON meta1.post_id = wp_posts.ID AND meta1.meta_key = 'oldpostid' AND wp_posts.post_type = 'post' RIGHT JOIN postslist meta2 ON meta1.meta_value = meta2.id
Read more here: MySQL find records that don’t exist comparing 2 tables