Postgresql – How to join two tables in postgresql

join;postgresql

First table

 Complaint_id   Status  Reopen
    127493          5   1
    127494          5   0

Second table

Parent_id   Currentstatus   Reopen  datetime    usertype
127493  0   0   2019-04-08 12:40:31.209103+05:30    c
127493  5   1   2019-04-08 12:40:31.209103+05:30    c
127493  0   0   2019-04-08 12:36:22.662995+05:30    c
127493  5   1   2019-04-08 12:36:22.662995+05:30    c
127493  0   0   2019-04-08 12:34:25.459444+05:30    c
127493  5   1   2019-04-08 12:34:25.459444+05:30    c
127493  5   0   2019-04-08 12:29:58.581771+05:30    E
127493  5   0   2019-05-08 12:29:58.581771+05:30    E

Output

complaintid currenstatus usertype
127493        0            c

I am trying using below query

SELECT
 currentstatus
FROM
  (
    SELECT
      Currentstatus,
      Datetime,
      Open_reopen_status,
      Parent_id,
      MAX(Datetime) OVER (PARTITION BY Parent_id) AS MaxDatetime
    FROM
      secondtable
  ) AS derived
WHERE Datetime = MaxDatetime

but i don't know how to connect with first table basically i want the records form first table is where reopen=1

select * from firsttable where reopen=1 

and fetch the current status and usertype from the second table according to max datetime

Best Answer

SELECT t1.Complaint_id,
       t2.currentstatus,
       t2.usertype
FROM firsttable t1
JOIN ( SELECT currentstatus, 
              usertype,
              Parent_id
       FROM ( SELECT Currentstatus,
                     Datetime,
                     Parent_id,
                     usertype,
                     MAX(Datetime) OVER (PARTITION BY Parent_id) AS MaxDatetime
              FROM secondtable
            ) AS derived 
       WHERE Datetime = MaxDatetime
     ) t2 ON t1.Complaint_id = t2.Parent_id
         AND t1.reopen = 1

?

PS. Does secondtable (Parent_id, Datetime) is unique by index/constraint?

PPS. Use CTE to make the query more readable...