SQL Query to Retrieve Woocommerce Order with Product Detail

Code

select
    p.ID as order_id,
    p.post_date,
    ids.ids,
    max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
    max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
    max( CASE WHEN pm.meta_key = '_order_tax' and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax,
    max( CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date,
    ( select group_concat( order_item_name separator '|' ) from wp_woocommerce_order_items where order_id = p.ID ) as order_items
from
    wp_posts p 
    join wp_postmeta pm on p.ID = pm.post_id
    join wp_woocommerce_order_items oi on p.ID = oi.order_id
    join
    (select order_id, group_concat( product_id separator '|' ) ids from
    (select i.order_item_name, i.order_id, im.meta_value product_id, im.meta_key FROM
    wp_woocommerce_order_items i join wp_woocommerce_order_itemmeta im
    where im.meta_key = '_product_id' and i.order_item_id = im.order_item_id) pds
    group by order_id) ids on p.ID = ids.order_id
where
    post_type = 'shop_order' and
    post_date BETWEEN '2023-04-01' AND '2023-05-01' and
    (post_status = 'wc-completed' OR post_status = 'wc-processing' )
group by
    p.ID;

reference

where clause for order with certain product IDs

replace PRODUCT_ID1&PRODUCT_ID2 to suit your needs

 ids REGEXP '.*(PRODUCT_ID1|PRODUCT_ID2).*'