How to fill the custom columns with joined values

I have a query like:

    SELECT `main_table`.*,
       IF(sosh.status = 'pending_payment', sosh.created_at, null)      AS `pending_payment_status_datetime`,
       IF(sosh.status = 'processing_payd', sosh.created_at, null)      AS `processing_payd_status_datetime`,
       IF(sosh.status = 'processing', sosh.created_at, null)           AS `processing_status_datetime`,
       IF(sosh.status = 'complete', sosh.created_at, null)             AS `complete_status_datetime`,
       IF(sosh.status = 'shipping_ready', sosh.created_at, null)       AS `shipping_ready_status_datetime`,
       IF(sosh.status = 'shipping_ready_cod', sosh.created_at, null)   AS `shipping_ready_cod_status_datetime`,
       IF(sosh.status = 'shipping_in_progress', sosh.created_at, null) AS `shipping_in_progress_status_datetime`,
       IF(sosh.status = 'closed', sosh.created_at, null)               AS `closed_status_datetime`,
       IF(sosh.status = 'archived', sosh.created_at, null)             AS `archived_status_datetime`,
       IF(sosh.status = 'withdraw', sosh.created_at, null)             AS `withdraw_status_datetime`,
       IF(sosh.status = 'canceled', sosh.created_at, null)             AS `canceled_status_datetime`
FROM `sales_order_grid` AS `main_table`
         LEFT JOIN `sales_order_status_history` AS `sosh` ON sosh.parent_id = main_table.entity_id
GROUP BY `main_table`.`entity_id`;

I have 2 tables:

sales_order_grid (where the main data is)

sales_order_status_history (where I want to attach the status data)

I need to fetch custom columns values of the latest statuses (I mean there could be more than one status for the order, but the date differs).

Could you please help me with that?

Answer

I assume you want an aggregation query:

SELECT `main_table`.*,
       SUM(sosh.status = 'pending_payment')  AS `pending_payment_status_datetime`,
       SUM(sosh.status = 'processing_payd') AS `processing_payd_status_datetime`,
       SUM(sosh.status = 'processing')           AS `processing_status_datetime`,
       SUM(sosh.status = 'complete')             AS `complete_status_datetime`,
       SUM(sosh.status = 'shipping_ready')       AS `shipping_ready_status_datetime`,
       SUM(sosh.status = 'shipping_ready_cod')   AS `shipping_ready_cod_status_datetime`,
       SUM(sosh.status = 'shipping_in_progress') AS `shipping_in_progress_status_datetime`,
       SUM(sosh.status = 'closed',)               AS `closed_status_datetime`,
       SUM(sosh.status = 'archived')             AS `archived_status_datetime`,
       SUM(sosh.status = 'withdraw' )             AS `withdraw_status_datetime`,
       SUM(sosh.status = 'canceled')             AS `canceled_status_datetime`
FROM `sales_order_grid``main_table` LEFT JOIN
     `sales_order_status_history` `sosh`
     ON sosh.parent_id = main_table.entity_id
GROUP BY `main_table`.`entity_id`;

This may fix the problems you are having.

EDIT:

You are asking for the latest statuses. Then the logic is:

SELECT so.*,
       MAX(CASE WHEN sosh.status = 'pending_payment' THEN sosh.created_at END) AS pending_payment_status_datetime,
       MAX(CASE WHEN sosh.status = 'processing_payd' THEN sosh.created_at END) AS processing_payd_status_datetime,
       . . . 
FROM sales_order_grid so LEFT JOIN
     sales_order_status_history `sosh`
     ON sosh.parent_id = so.entity_id
GROUP BY so.entity_id;