How to select data between two datetimes which are related to certain ID in postgresql-CodePudding

So I have 2 tables. In first table I need to link ID with min and max datetime, which I did with

SELECT id, min (datetime) as earlytime, max(datetime) as latesttime
FROM table1
group by id

as a result I get around 5k with ID and 2 timestamps.

I need to use this info to get rows with ID from table2 where datetime for each ID respectively is between their earlytime and latesttime. Or just rows with ID and time which less than latesttime.

Still can't figure out how to do that.

Came up with something like that

SELECT ID, source, amount, type, datetime
FROM table2
WHERE EXISTS (SELECT ID, min (datetime) as earlytime, max (datetime) as latetime
FROM table1
group by ID)

But I guess it just shows any rows that match ID from table1

Anyone can help me with that?

CodePudding user response：

maybe you can join and get the results like below

select A.* 
from table2 A 
join 
(
SELECT id, min (datetime) as earlytime, max(datetime) as latesttime
FROM table1
group by id
)B
on A.id=B.id and 
B.timecol between earlytime and latesttime

CodePudding user response：

You can use a combination of a CTE or sub-query (whichever you prefer) and BETWEEN()to achieve your expected output

with cte as (
   select
      id
      ,min(datetime) as earlytime
      ,max(datetime) as latesttime
   from table1
   group by id
)

select
   c.id
   ,c.earlytime
   ,c.latesttime
   ,t2.* /*Table2 columns*/
from cte as c
inner join table2 as t2 ON c.id = t2.id 
  and t2.datetime between c.earlytime and c.latesttime

CodePudding user response：

SELECT T2.ID,T2.SOURCE,T2.AMOUNT,T2.TYPE,T2.DATETIME
FROM TABLE2 AS T2
JOIN
(
  SELECT id, min (datetime) as earlytime, max(datetime) as latesttime
  FROM table1
  group by id
)X ON T2.ID=X.ID AND T2.DATETIME BETWEEN X.earlytime AND X.latesttime