How to unify 2 consultations?

Question

how can I unify two Querys? I have two queries to show different data see:

#This shows the course related data

SELECT  wu.ID,
        wu.display_name as Usuário,
        wu.user_email AS Email, 
        wc.comment_post_ID AS ID_do_Curso,  
        wt.post_title AS Curso,
        CASE wc.comment_approved 
        WHEN 'complete' THEN 'Concluído'
            WHEN 'in-progress' THEN 'Em Andamento'
            WHEN 'complete' THEN 'Concluído'
            ELSE 'Unknow'
            END as 'Status',
        CASE wm.meta_key
          WHEN 'start' THEN 'Data da Matrícula'
            WHEN 'percent' THEN 'Porcentagem concluída'
            WHEN 'complete' THEN 'Aulas assistidas'
            ELSE 'Unknow'
            END as 'Infos',
        wm.meta_value 
        
FROM    wp_comments wc,
        wp_users wu,
        wp_commentmeta wm,
        wp_posts wt

WHERE   wc.comment_type = 'sensei_course_status' AND 
        wc.user_id = wu.ID AND
        wm.comment_id = wc.comment_ID AND
        wc.comment_post_ID = wt.ID

ORDER BY `wu`.`display_name` ASC

#This shows the data related to the product and coupon

SELECT
  p.ID AS 'ID do pedido',
  p.post_date AS 'Data de compra',
  MAX( CASE WHEN pm.meta_key = '_billing_email'       AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'E-mail',
  MAX( CASE WHEN pm.meta_key = '_billing_first_name'  AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Nome Completo',
  MAX( CASE WHEN pm.meta_key = '_billing_last_name'   AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Empresa',
  MAX( CASE WHEN pm.meta_key = '_billing_address_1'   AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Endereço',
  MAX( CASE WHEN pm.meta_key = '_billing_city'        AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Cidade',
  MAX( CASE WHEN pm.meta_key = '_billing_state'       AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Estado',
  MAX( CASE WHEN pm.meta_key = '_billing_postcode'    AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'CEP',
    CASE p.post_status
      WHEN 'wc-pending'    THEN 'Pagamento pendente'
      WHEN 'wc-processing' THEN 'Em processamento'
      WHEN 'wc-on-hold'    THEN 'Em espera'
      WHEN 'wc-completed'  THEN 'Concluído'
      WHEN 'wc-cancelled'  THEN 'Cancelado'
      WHEN 'wc-refunded'   THEN 'Devolveu'
      WHEN 'wc-failed'     THEN 'Falhou'
    ELSE 'Unknown'
    END AS 'Status da compra',
  MAX( CASE WHEN pm.meta_key = '_order_total'         AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Total da Compra',
  MAX( CASE WHEN pm.meta_key = '_paid_date'           AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Data de pagamento',
  ( select group_concat( order_item_name separator '</p>' ) FROM wp_woocommerce_order_items where order_id = p.ID ) AS 'Itens encomendados'
FROM  wp_posts AS p
JOIN  wp_postmeta AS pm ON p.ID = pm.post_id
JOIN  wp_woocommerce_order_items AS oi ON p.ID = oi.order_id
WHERE post_type = 'shop_order'
GROUP BY p.ID

Answer 1

I don’t know how I could do this, I tried the following but I get duplicate users and null results

SELECT wu.ID, wc.user_id, wu.display_name, 
wpo.ID as 'ID C e L',
wpo.post_title as Curso, 
wpo.post_modified_gmt,
wpo.post_type as Tipo, 
wc.comment_ID, wc.comment_post_ID, wc.comment_type,
wcm.comment_id, wcm.meta_key, wcm.meta_value,
woi.order_item_id, woi.order_item_name,
woim.meta_key, woim.meta_value
FROM wp_posts wpo
LEFT JOIN wp_postmeta wpm ON wpo.ID = wpm.post_id
LEFT JOIN wp_comments wc ON wpo.ID = wc.comment_post_ID
and wc.comment_type = 'sensei_course_status'  
LEFT JOIN wp_commentmeta wcm ON wc.comment_ID = wcm.comment_id
LEFT JOIN wp_users wu ON wc.user_id = wu.ID 
LEFT JOIN wp_usermeta wum ON wu.ID = wum.user_id
LEFT JOIN wp_woocommerce_order_items woi ON wpo.ID = woi.order_id
LEFT JOIN wp_woocommerce_order_itemmeta woim ON woi.order_item_id = woim.order_item_id
WHERE  wpo.post_type = 'course' OR wpo.post_type = 'shop_order'  
ORDER BY `wpo`.`ID` ASC

Answer 2

You have to have something that links one table to another. Using wp_post, in the first query you select the id of this table, which will allow JOIN with the second query when the id of the wp_post table is equal for example. You have to analyze... you can mount a data view as well or even a subquery.