SQL Query to get post_id from wp_posts and and meta_key(s) from wp_postmeta

Question

I have a custom post type called media_coverage and need to export via PHPMyAdmin some of the data in that post type that is in wp_posts (post_title) and also in associated wp_postmeta (meta_key) keys.

I obviously need to use the post_id, but I don't know how to 1) dynamically pass the post_id in the query to get the data from the wp_posts table and the post_id associated data from the wp_postmeta tables.

And, 2) I don't know how to get multiple meta_key values at the same time from wp_postmeta.

This is what I've worked through in PHPMyAdmin:

• This query shows me all the posts and post_id's, etc, for the media_coverage custom post type:

SELECT* FROM wp_posts WHERE post_type = 'media_coverage'

• The post_title is one bit of data I need to retrieve; this shows me all the post titles of all the media_coverage posts from wp_posts:

SELECT post_title FROM wp_posts WHERE post_type = 'media_coverage'

• But what I need to do is also retrieve multiple meta_key values from wp_postmeta; this gives me one of the meta_keys called coverage_url:

SELECT* FROM wp_postmeta WHERE post_id = '82080' AND meta_key = 'coverage_url'

But that's only from post ID 82080, as I don't know how to have the query use all of the post_id's of available media_coverage posts.

So how do I construct a query like this:

SELECT* FROM wp_posts WHERE post_type = 'media_coverage' AND

(pseudo code)

the post_title from that post_id in wp_posts

and the meta_key = 'source_name' from that post_id in wp_postmeta

and the meta_key = 'coverage_url' from that post_id in wp_postmeta

Edit 9/14/22

This query works somewhat; I get the columns of coverage_url and source_name, but I also get all the columns of each post, like draft status, date published, revisions, etc., so limiting the output to post_id, coverage_url and source_name would be nice.

SELECT wp_posts.*, 
a.meta_value source_name, 
b.meta_value coverage_url
FROM wp_posts
LEFT JOIN wp_postmeta a ON wp_posts.ID = a.post_ID AND a.meta_key='source_name'
LEFT JOIN wp_postmeta b ON wp_posts.ID = b.post_ID AND b.meta_key='coverage_url'
WHERE wp_posts.post_type = 'media_coverage';

Answer 1

You can do something like

• Query the wp_posts table
• Left join the source_name from wp_posmeta table
• Left join the coverage_url from wp_posmeta table

Then select the data you want to pull from post, source_name and coverage_url result

Something like this should do

SELECT 
    post.ID, 
    post.post_title, 
    sn.meta_value as source_name, 
    cu.meta_value as coverage_url

FROM wp_posts as post

LEFT JOIN wp_postmeta as sn
    ON  post.ID = sn.post_id
    AND sn.meta_key = 'source_name'

LEFT JOIN wp_postmeta as cu
    ON  post.ID = cu.post_id
    AND cu.meta_key = 'coverage_url'

WHERE post.post_type = 'media_coverage'

You Query output should be somethng like

---- ID ----+---- post_title ----+---- source_name ----+---- coverage_url
-------------------------------------------------------------------------
   1        |     Title Here     |   Source Name Here  |  Coverage URL Here