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... JOIN
s is that you are SELECT
ing 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_key
s "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");