SQL Query for Custom Post Type and Multiple Custom Fields

Question

I am using WP Data Tables to create a table from the SQL DB. In the wordpress backend the example code to use looked like this:

SELECT post_id, post_date
FROM wp_posts
WHERE post_type =  'custom_post_type'
AND post_status =  'publish'

Im trying to get custom field values from the post meta. Here is what I have so far...

SELECT post_id, post_date
FROM wp_posts
WHERE post_type =  'custom_post_type'
AND post_status =  'publish'
AND SELECT custom_field_key_1, custom_field_key_2, custom_field_key_3
FROM wp_postmeta
WHERE post_id = post_id

UPDATE:

I found that p.ID was needed instead of post_id and that I need search for the meta_key. Something like...

SELECT p.post_title, 
       p.post_date,
       pm.meta_key = 'custom_field_key'
FROM wp_posts p 
INNER JOIN wp_postmeta pm 
ON p.ID = pm.post_id
WHERE p.post_type = 'custom_post_type'
AND p.post_status = 'publish'

Answer 1

Use an INNER JOIN:

SELECT p.post_id, 
       p.post_date, 
       pm.custom_field_key_1, 
       pm.custom_field_key_2, 
       pm.custom_field_key_3
FROM wp_posts p 
   INNER JOIN wp_postmeta pm 
       ON p.post_id = pm.post_id
WHERE p.post_type = 'custom_post_type'
   AND p.post_status = 'publish'