CODEWITHSUNDEEP
sqlpostgresqljoinselectnested
Ashley O
Ashley O

Reputation: 1190

Join One-To-Many, return minimum greater-than result

I know the headline is complicated, but here is the situation:

I have a table of users like this:

 id     opened_at                
 32o   2/01/2018 00:00:00

And a table of users-groups like this:

 user_id     group_id
  32o         29834jb

And a full table of groups like this:

 group_id               group_ended_at
 29834jb               1/14/2018 00:00:00
 29834jb               2/14/2018 00:00:00
 29834jb               3/14/2018 00:00:00
 29834jb               4/14/2018 00:00:00

I want to join groups to users, but only return the minimum group_ended_at that is greater than the opened_at date for the user.

In other words, I want this result:

id           opened_at              group_id              group_ended_at
32o         2/01/2018 00:00:00       29834jb            2/14/2018 00:00:00

How would I go about that?

Upvotes: 1

Views: 32

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This answers the question that was originally asked.

I like a lateral join for this:

SELECT u.*, g.group_ended_at
FROM users u LEFT JOIN LATERAL
     (SELECT MIN(g.group_ended_at) as group_ended_at
      FROM groups g
      WHERE u.group_id = g.group_id AND g.group_ended_at > u.opened_at
     ) g;

Upvotes: 0

GMB
GMB

Reputation: 222482

We can build on your attempt: just add the date inequality condition to the join conditions, define the proper group by clause, and you are here:

SELECT u.id, u.opened_at, u.group_id, MIN(g.group_ended_at)
FROM users u
LEFT JOIN groups g
    ON  u.group_id = g.group_id
    AND g.group_ended_at > u.opened_at
GROUP BY u.id, u.opened_at, u.group_id

Upvotes: 2

Related Questions