How to write a sql query to get following result?

Question

I'm trying to get attachment post ids (post_id) from Wordpress wp_postmeta table which not associated as thumbnail for a post.

currently I'm getting all the attachments using below query

select * from wp_postmeta where meta_key = '_wp_attached_file'

which gives results like below

then I'm getting posts which already have a attachment using below query

select * from wp_postmeta where meta_key = '_thumbnail_id'

which gives results like below

by comparing these results, I'm getting the unattached files

which are these highlighted rows

Instead of running two queries and comparing it using PHP, is there way to get a result i wanted using a one MySQL query? if so any help would be appreciated

Regards

Answer 1

This should work.

select t1.* from wp_postmeta t1 where t1.meta_key = '_wp_attached_file' AND t1.post_id NOT IN (select t2.meta_value from wp_postmeta t2 where t2.meta_key = '_thumbnail_id')

Notice that this query has performance issues because of nested query.