How to Count Times an ID Appears in a Column in a Different Table

Apologies in advance: I’m sure this is relatively easy and has been asked ad nauseam, but I just can’t quite come up with the proper search.

Basically, I’m trying to take a list of IDs queryed from one table, and then see which ones DO NOT appear in a separate column in another table.

It’s a WordPress MySQL database. Authors are attached to a Post via metadata. Both authors and posts are considered to be posts for database purposes. There is one table containing both posts/authors: wp_posts/wp. There is another table containing the metadata attached to a post/author: wp_postmeta/wm.

I’m attempting to take a list of authors from wp_posts and see which ones are orphans, i.e. not attached to a post, by checking a column called meta_value in wp_postmeta.

An Author is tied to a Post by having the Author’s ID from wp_posts show up in the wm.meta_value column for a Post. But Authors themselves are declared by having an 'author' value in the same column. So an Author with an ID of 17078 will have an 'author' value in wm.meta_value, while a Post attributed to that Author will have 17078 in wm.meta_value.

The following query gets me about halfway there by returning all the Authors that we have in our database:

    SELECT 
        post_title,
        ID
    FROM
        wp_posts wp
    JOIN
        wp_postmeta wm
    ON
        wp.ID = wm.post_id
    WHERE
        wm.meta_value = 'author'

I need to somehow take that returned list and highlight which of those IDs do not show up in the wm.meta_value column for all the posts. Any suggestions or guidance would be greatly appreciated.

Answer

Do you need below –

SELECT ID
FROM wp_postmeta wm
WHERE wm.meta_value = 'author'
AND NOT EXISTS (SELECT 1
                FROM wp_postmeta wm2
                WHERE wm2.ID = wm.ID)

Advertisement

Answer