How to merge two tables to show entries historical changes-CodePudding

I have two tables

T1:

ID	Reference	Status	Event Timestamp
1	Flowers	Dispatched	2021-12-10
2	Flowers	Delivered	2021-12-11

And T2:

ID	Reference	Comments	Event Timestamp
1	Flowers	well done	2021-12-12
2	Flowers	go on	2021-12-13
3	Pot	random	2021-12-13

The table I'm trying to have by querying for Flowers reference is (using Postgres)

t1_ID	t2_ID	Reference	Comments	Status	t1_Event Timestamp	t2_Event Timestamp
1	null	Flowers	null	Dispatched	2021-12-10	null
2	null	Flowers	null	Delivered	2021-12-11	null
null	1	Flowers	well done	Delivered	2021-12-11	2021-12-12
null	2	Flowers	go on	Delivered	2021-12-11	2021-12-13

In other words, I need to have a joined table that records all the update between the two tables.
I tried many queries, like LEFT JOIN, UNION, etc. however all attempts are unsuccessful.

Could you suggest me which SQL statements should I use to obtain the expected result?

CodePudding user response：

You can use a union between rows from T1 with a join between T1 and T2 on closest date:

select "ID" as t1_id,
null as t2_id, 
"Reference", 
null as "Comments", 
"Status",
"Event Timestamp" as "t1_Event Timestamp", 
null as "t2_Event Timestamp"
from T1

union all

select T1."ID" as t1_id,
T2."ID" as t2_id, 
T1."Reference", 
"Comments", 
"Status",
T1."Event Timestamp" as "t1_Event Timestamp", 
T2."Event Timestamp" as "t2_Event Timestamp"
from T1 inner join T2
on T1."Event Timestamp" = 
   (select max("Event Timestamp") from T1 as t where t."Event Timestamp" < T2."Event Timestamp" and t."Reference" = T2."Reference")
   and T1."Reference" = T2."Reference"
where T1."Reference" = 'Flowers'

Fiddle

If you really want null as t1_id in the last two rows you can just replace T1."ID" with null in the second select:

select null as t1_id,

CodePudding user response：

Use cte and union

with cte as(
   select ID as t1_ID,
          null as t2_ID,
          Reference,
          null as Comments,
          Status,
          Event_Timestamp as t1_Event_Timestamp,
          null as t2_Event_Timestamp,
          row_number() over(partition by Reference order by Reference) seq
    from t1
)

select t1_id,t2_id,reference,comments,status,t1_event_timestamp,t2_event_timestamp  
from cte

union all

select null as t1_ID,
       t2.ID::varchar(10) as t2_ID,
       t2.Reference,
       Comments,
       (select max(Status) from cte t3 where t3.seq = (select max(seq) from cte)) as status,
       (select max(t1_Event_Timestamp) from cte t3 where t3.Reference = t2.Reference) as t1_Event_Timestamp,
       t2.Event_Timestamp as t2_Event_Timestamp
from t1
left join t2 on t1.Reference = t2.Reference
group by t2.ID,t2.Reference,t2.Comments,t2.Event_Timestamp

Demo in db<>fiddle