How to JOIN postmeta table and get the posts before the date of custom field using SQL

I’d like to combine wp_posts table and wp_postmeta table and get the posts before the date of custom field.

Code with only wp_posts table is below and it works fine

$posts = $wpdb->get_results(
    "SELECT DISTINCT
    MONTH( post_date ) AS month,
    YEAR( post_date ) AS year
    FROM $wpdb->posts
    WHERE post_status = 'publish'
    AND post_date <= now( )
    AND $wpdb->posts.post_type = 'event'
    GROUP BY month , year
    ORDER BY year DESC, month ASC
    ");

I want to add “postmeta table” to “post table” and get only posts before the date of custom field

if it is “WP_Query”, meta query would be below

  'meta_query' => array(
    array(
        'key' => 'end_date',
        'value' => date('Y/m/d'),
        'type' => 'DATE',
        'compare' => '>=',
    )
  )`

I tried code below but it returns empty array.

$posts = $wpdb->get_results(
    "SELECT DISTINCT
    MONTH( post_date ) AS month,
    YEAR( post_date ) AS year
    FROM $wpdb->posts
    LEFT JOIN $wpdb->postmeta
    ON $wpdb->postmeta.post_id
    WHERE post_status = 'publish'
    AND post_date <= now( )
    AND $wpdb->posts.post_type = 'event'
    AND $wpdb->postmeta.meta_key = 'end_date'
    AND $wpdb->postmeta.meta_value = date('Y/m/d')
    AND $wpdb->postmeta.meta_type = 'DATE'
    AND $wpdb->postmeta.meta_compare = '>='
    GROUP BY month , year
    ORDER BY year DESC, month ASC
    ");

When it is added only meta_key, it returns the same result as “Code for only wp_posts table”(first code)

//same result as "Code for only wp_posts table"
$posts = $wpdb->get_results(
    "SELECT DISTINCT
    MONTH( post_date ) AS month,
    YEAR( post_date ) AS year
    FROM $wpdb->posts
    LEFT JOIN $wpdb->postmeta
    ON $wpdb->postmeta.post_id
    WHERE post_status = 'publish'
    AND post_date <= now( )
    AND $wpdb->posts.post_type = 'event'
    AND $wpdb->postmeta.meta_key = 'end_date'
    GROUP BY month , year
    ORDER BY year DESC, month ASC
    ");

I’d like to know how to add conditions “key”, “value” and “compare”.

I’m very beginner on SQL and hope someone help me.

Answer

The changes I see between your original and new queries are the LEFT JOIN and the added conditions to your WHERE clause. Adding a LEFT JOIN to your initial query won’t result in an empty array unless one of these is true:

1) Your original query returned 0 rows
2) Your new query has additional filtering in the WHERE clause

If you want to return all your initial rows (i.e. from $wpdb->posts) and include any matching rows from $wpdb->postmeta, then just move the extra AND conditions from the WHERE clause to the JOIN:

LEFT JOIN $wpdb->postmeta
ON $wpdb->postmeta.post_id
-- join conditions
AND $wpdb->postmeta.meta_key = 'end_date'
AND $wpdb->postmeta.meta_value = date('Y/m/d')
AND $wpdb->postmeta.meta_type = 'DATE'
AND $wpdb->postmeta.meta_compare = '>='
-- filter conditions
WHERE post_status = 'publish'
AND post_date <= now( )
AND $wpdb->posts.post_type = 'event'