MYSQL Case statement issue when join table

Question

I have two tables called history and updates. If need to update History table values, then updated values insert into update table with Key_id.

Key_id mapped with History table columns.

In here History table contains start_date and close_date. When we need to update history table particular row start_date, Then Assume history table id is 243247, and start_date need to update as '2021-11-02' then update table id will auto generate, new_value field to '2021-11-02' and Key_id to 1

Key_id = 1 means start_date
key_id = 4 means close_date

Please see the below images

History Table

Updates table

Question: I need to get all the records with history table, if update table contains values related to history table, then need to get only the latest value and only get in a single row in history table

For an example history table first row to we have 5 updated values in updates table. I need to assign last updated values(both start_date and close_date) to first row in history table.

Here is the query I have tried

SELECT ch.id,  
     (CASE cu.key_id
     when  1
        then cu.new_value
        else ch.start_date
    END)AS start_date,
    
    (CASE cu.key_id
        when 4 
        then cu.new_value 
        else ch.close_date
    END)AS close_date 
    
     FROM  history ch
    LEFT JOIN  _updates  cu 
    ON ch.id = cu.history_id    
    group by ch.id
    ORDER BY cu.id  DESC;

My issue is, this query return only single value from update table. I need to get both updated latest values in a single row. Please tell me how to resolve this issue

MYSQL Case statement issue when join table

0 Answers