SELECT custom post type and its meta in SQL

Question

So I have the following SQL script where I can view all my post_meta with specific meta_keys:

SELECT * FROM `wp_postmeta` 
WHERE `meta_key` = 'honorific' 
OR `meta_key` = 'suffix'
OR `meta_key` = 'nickname' 

Is there anyway that I can make it where instead of giving me ALL the post_meta with those keys, it gives me just the post_metas that match a specific post type such as 'test'?

I tried:

SELECT * p.post_type FROM wp_posts p ` INNER JOIN `wp_postmeta`

----- Also:

SELECT p.ID, 
       p.post_title
FROM  wp_posts p 
      INNER JOIN wp_postmeta AS pm ON pm.post_id  = p.ID
WHERE
      pm.meta_key = 'honorific' AND p.post_type = 'employee'

That's where I'm kinda of stuck at - All help will be appreciated.

Basically:

• Give me post_meta that equals honorific, suffix and nickname with posts related to custom post type test.

Answer 1

Tom's comment on the question makes a very important point: unless you are just using this SQL for diagnostic or exploratory purposes, there is almost definitively an easier and more efficient means to access this data, either through the WordPress APIs or via tools like WP CLI.

There is virtually no reason to use this SQL for logic within a WordPress plugin, theme, or site.

The way to think of SELECT... JOINs is that you are SELECTing from the "virtual table" produced by the JOIN.

We can SELECT all of the rows from the wp_postmeta table which have one of the meta_keys "honorific", "suffix", or "nickname" and a post_id field corresponding to the ID field in wp_posts rows with the post_type "employee" as such:

SELECT wp_postmeta.*
  FROM wp_posts INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
  WHERE wp_posts.post_type="employee"
    AND wp_postmeta.meta_key IN ("honorific", "suffix", "nickname");

Or more succinctly with table aliases,

SELECT m.*
  FROM wp_posts AS p INNER JOIN wp_postmeta AS m ON p.ID = m.post_id
  WHERE p.post_type="employee"
    AND m.meta_key IN ("honorific", "suffix", "nickname");