How to make column data from row data relationship in codeigniter

Question

I have some table like this in codeigniter query

regist_header with column: id, request_id, regist_number, created_date, etc

and have table for activity regist_header, like this
document_status with column: regist_header_id, status, date

this is my regist_header regist_header and this is my document_status document_status

from that relationship, i wanna make data like this in one row each data

id | request_id | registration_number | submit_date | approve_date | activation date

Thankyou

Answer 1

Based on the document_status data, it looks like you have multiple entries for the same status. So do you want to show latest records or earliest records? Based on the latest records, that query could help:

select rh.id, rh.request_id, rh.registration_number, ds.submit_date, ds.approve_date, ds.activation_date
  from regist_header rh
  left 
  join (
        select regist_header_id,
               max(case when status = 'submit' then max_date end) as submit_date,
               max(case when status = 'approve' then max_date end) as approve_date,
               max(case when status = 'activation' then max_date end) as activation_date
          from document_status ds
         group by regist_header_id
       )ds
    on rh.id = ds.regist_header_id