MySql : how to join this two tables with multiple pivot

I want to get the last activity of my client but i dont know how to that that with two tables that have more than one pivot. Please look at to the example below :

table product
--------------------------------------------------------------------------------------------------------------------------------------
   id        |      name       |   check_mo (Activity1)   | check_mo_account_id  | check_pa (Activity2)    | check_pa_account_id
--------------------------------------------------------------------------------------------------------------------------------------------
    1        |      product1   |   01/02/2020             |         63           |  05/02/2020            |         100
    2        |      product2   |   01/03/2020             |         23           |  10/03/2020            |        63
-----------------------------------------------------------------------------------------------------------------------------------

Table account

--------------------------------
   id        |      name         
--------------------------------
    23        |      name1   
    63        |      name2
    100       |      name3  
--------------------------------

I want this result (last activity is the lastest date of (check_mo and check_pa). and relationship between tables is (account.id => product.check_mo_account_id and product.check_pa_account_id))

 ------------------------------------------------
   id        |      name         | last activity
-------------------------------------------------
    23        |      name1       |   01/03/2020
    63        |      name2       |   10/03/2020
    100       |      name3       |   05/02/2020
-------------------------------------------------

Answer

If I understand correctly, you have two check IDs and two check dates in one row, but want to treat them equally, just as if you had just one table with one check ID and one check date per row. Use UNION ALL to get this table. Then find the maximum date per ID and join this to the account table.

select id, account.name, aggregated.last_activity
from account
join
(
  select id, max(check) as last_activity
  from
  (
    select check_mo_account_id as id, check_mo as check from product
    union all
    select check_pa_account_id as id, check_pa as check from product
  ) unioned
  group by id
) aggregated using (id)
order by id;