Query posts with a meta key of post ID whose post no longer exists

Hi,

Can you please share code you have try?

Hi,
@umchal

I have created the solution by my understanding for this, code is below, Hope it may help you
also, you can change variables according to your requirement.

global $wpdb;
	$books = $wpdb->get_results("SELECT post_id,meta_value FROM $wpdb->postmeta WHERE (meta_key = '_shop_id')");
	$book_ids = array();
	foreach($books as $book){
		if ( 'publish' != get_post_status ( $book->meta_value ) ) {
			$book_ids[] = $book->post_id;
		}
	}

	foreach($book_ids as $book_id){
		$book_post = get_post($book_id); // Result
		//echo '<pre>'; print_r($book_post);
	}

Thanks.

Ah, actually it doesn’t check whether the shop post exists or not, which is required.

And I’m looking for something more efficient like this one, like with one query.

SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL

But thanks for the help though.

Figured it!

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts
LEFT JOIN wp_postmeta AS mt ON ( wp_posts.ID = mt.post_id AND mt.meta_key = '_shop_id' )
LEFT JOIN wp_posts    AS p2  ON ( mt.meta_key = '_shop_id' AND mt.meta_value = p2.ID )
WHERE 1=1  
AND (
    mt.meta_key IS NOT NULL
    AND                 
    p2.ID IS NULL                
)
AND wp_posts.post_type = 'book'
AND ((wp_posts.post_status <> 'trash' AND wp_posts.post_status <> 'auto-draft'))
GROUP BY wp_posts.ID

Great…..!