MySQL - 如果键/值对不存在，则忽略WHERE条件

Question

In a Wordpress order based system I am selecting people going to an event on a certain date and location.

I also need to see if there are any order notes existing for any matching orders, which I do by selecting the meta_value where the meta_key ="_wc_acof_2".

The problem is when I add the condition to my WHERE clause the code stops returning records where that meta key/value pair doesn't exist (i.e. there are no order notes). Whereas I still need to select those records just return null or similar for the order notes.

The problem line in the WHERE clause is:

AND jn_postmeta_guestnotes.meta_key = "_wc_acof_2"

In my head I would like to write that line similar to:

AND (jn_postmeta_guestnotes.meta_key = "_wc_acof_2" OR 'there is no matching meta_key so just ignore this condition and return the record anyway')

The full query:

SELECT 
    CONCAT_WS(" ",wp_postmeta.meta_value,jn_postmeta_lastname.meta_value) AS Name,
    wp_woocommerce_order_itemmeta.meta_value AS Adults,
    jn_postmeta_guestnotes.meta_value AS Notes
 FROM
    wp_postmeta
        LEFT JOIN
    wp_postmeta AS jn_postmeta_lastname ON wp_postmeta.post_id = jn_postmeta_lastname.post_id
        LEFT JOIN
    wp_postmeta AS jn_postmeta_guestnotes ON wp_postmeta.post_id = jn_postmeta_guestnotes.post_id
        LEFT JOIN
    wp_posts ON wp_postmeta.post_id = wp_posts.ID
        LEFT JOIN
    wp_woocommerce_order_items ON wp_woocommerce_order_items.order_id = wp_posts.ID
        LEFT JOIN
    wp_woocommerce_order_itemmeta ON wp_woocommerce_order_itemmeta.order_item_id = wp_woocommerce_order_items.order_item_id
        LEFT JOIN
    wp_woocommerce_order_itemmeta AS jn_woocommerce_order_itemmeta_location ON wp_woocommerce_order_itemmeta.order_item_id = jn_woocommerce_order_itemmeta_location.order_item_id
        LEFT JOIN
    wp_woocommerce_order_itemmeta AS jn_woocommerce_order_itemmeta_date ON wp_woocommerce_order_itemmeta.order_item_id = jn_woocommerce_order_itemmeta_date.order_item_id
 WHERE
    1 = 1
        AND wp_postmeta.meta_key = "_billing_first_name"
        AND jn_postmeta_lastname.meta_key = "_billing_last_name"
        AND jn_postmeta_guestnotes.meta_key = "_wc_acof_2"
        AND wp_woocommerce_order_itemmeta.meta_key = "Adults"
        AND jn_woocommerce_order_itemmeta_location.meta_key = "Booking Type"
        AND jn_woocommerce_order_itemmeta_location.meta_value LIKE "%'.$showlocation.'%"
        AND jn_woocommerce_order_itemmeta_date.meta_key = "Booking Date"
        AND (jn_woocommerce_order_itemmeta_date.meta_value = "'.$showdateformata.'" OR jn_woocommerce_order_itemmeta_date.meta_value = "'.$showdateformatb.'")
        AND wp_posts.post_status = "wc-completed"
 GROUP BY wp_posts.ID

Any advice much appreciated!

Answer 1

The answer is to put the extra bit in a sub-select like so:

(SELECT wp_postmeta.meta_value FROM wp_postmeta WHERE wp_postmeta.meta_key = "_wc_acof_2" AND wp_postmeta.post_id = wp_posts.ID) AS Notes