How to do a looping select union in Postgres

Question

I'm trying to do select from Table2 for each row in an initial select statement from Table1.

Initial select statement would look something like

Select * from Table1 Where GroupID='someId' AND ObjectID='someID'

Table1 would return something like the following (there will only ever be 0-1 returned rows with a null EndDate value).

Table2 will look as follows

Basically my goal is to loop through the first select statements rows. For each row I want to take the objectid, startdate, and enddate to select all the appropriate rows in Table2. Then after do a UNION(?) on all the data I've selected from Table2. I want to be able to select all the rows in Table2 which's objectid matches the objectid given and the timestamp is in-between the start/end dates (or to current timestamp if enddate is null). Does that make sense? I've been looking online, but have not found a way to achieve a looping select union like this.

I'm not sure if this is possible in a single select statement or if a stored proc is needed/cleaner (either is fine but I'd rather avoid stored proc if I can).

forpas · Accepted Answer · 2020-11-20 17:17:38Z

1

You can do it with a join:

SELECT t2.*
FROM Table2 t2 INNER JOIN Table1 t1
ON t1.objectid = t2.objectid 
AND t2.timestamp BETWEEN t1.startdate AND COALESCE(t1.enddate, current_timestamp)
WHERE t1.GroupID = 'someId' AND t1.ObjectID = 'someID'

answered Nov 20, 2020 at 17:17

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Regis Over a year ago

This works beautifully, thank you! Wasn't aware of the COALESCE function so thank you for that as well.

Collectives™ on Stack Overflow

How to do a looping select union in Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related