SELECT * FROM wp_posts AS posts WHERE
post_type = 'shop_order' AND
post_status = 'wc-cancelled' AND
posts.ID IN
(SELECT comment_post_ID FROM wp_comments WHERE
comment_content LIKE '%Awaiting REDSYS payment Order status changed from Pendiente de pago to Cancelado%' OR
comment_content LIKE '%Awaiting REDSYS payment Order status changed from Pending Payment to Cancelled%' OR
comment_content LIKE '%Awaiting REDSYS payment Order status changed from Procesando to Cancelado%' OR
comment_content LIKE '%Awaiting REDSYS payment Order status changed from Partially paid to Cancelado%' OR
comment_content LIKE '%Awaiting REDSYS payment Order status changed from En espera to Cancelado%' OR
comment_content LIKE '%Awaiting redsys payment Order status changed from Processing to Cancelled%' OR
comment_content LIKE '%Awaiting redsys payment El estado del pedido ha cambiado de Pendiente de pago a Cancelado%')
ORDER BY post_date DESC;