Search code examples
postgresqljoin

Postgre SQL: Conditional join between tables and leave the nearest date only

I am pulling this query os user 'USv3s_tggf19' in a PostgreSQL database server.

select us.id as user_step_id, us.goal_id, us.user_id, us.created_datetime
, m.mod_code, m.modified_datetime
from database.user_steps us
left join database.mods m on
us.goal_id = m.goal_id 
where us.user_id like 'USv3s_tggf19'

An this is the table that I have:

output

As you can see, because in the database.mods table there are duplicated records that relate to different modifications of the same thing, I end up having duplicate records in my output, which is expected.

Now, my intention is to leave the record that is closest in m.modified_date to us.created_datetime. And my end result should look something like this:

desired_output

I've been trying different things looking in other responses and this query is the one that looks closest, but it does not return me any information from the mods table.

select us.id as user_step_id, us.goal_id, us.user_id, us.created_datetime
, m.mod_code, m.modified_datetime
from database.user_steps us
left join database.mods m on
us.goal_id = m.goal_id 
and us.created_datetime = (select max(m.modified_datetime) from database.mods m  where m.modified_datetime < us.created_datetime)
where us.user_id like 'USv3s_tggf19'

I'd appreciate any guidance with this.

Solution

  • Working dbfiddleuk example

    I dumbed down the data to help me understand it a bit; but this SQL should be close.

    More info on Lateral

    • Prior to lateral design pattern, one would use subqueries and row numbers and coorlation to do this.

    • Prior to using row_number one would use a inline view to get the max/min date for each goal_id and then join this back to the base set from mods to get all the columns.

    Just different design patterns over the ages.

    Lateral basically says:

    -- For each row in user_Steps, get the related mods for that goal (correlation) having the earliest modified date (again correlation) after the created date.

    SELECT us.id as user_step_id, us.goal_id, us.user_id, us.created_datetime
, m.mod_code, m.modified_datetime
FROM user_steps us
LEFT join LATERAL (SELECT mod_Code, goal_ID, modified_datetime 
                   FROM mods a 
                   WHERE a.modified_DateTime < us.created_datetime
                     and us.goal_id = a.goal_id 
                   ORDER BY a.modified_DateTime desc
                   LIMIT 1) m
  on true 
WHERE us.user_id = 'A'

    For future reference you could use the dbfiddle.uk to mock up an example or use https://ozh.github.io/ascii-tables/ to mock up text tables.

    Giving us: (I used ascii tables (github.io link above) to generate via a copy and paste results from the dbfiddle.uk)

    +--------------+---------+---------+------------------+----------+-------------------+
| user_step_id | goal_id | user_id | created_datetime | mod_code | modified_datetime |
+--------------+---------+---------+------------------+----------+-------------------+
| A            | A.1     | A       | 2024-05-10       | B2       | 2023-09-08        |
| B            | B.1     | A       | 2024-04-26       | I10      | 2024-02-02        |
| C            | C.1     | A       | 2024-03-12       | B5       | 2023-09-08        |
| D            | D.1     | A       | 2024-03-01       | I10      | 2024-02-02        |
| E            | E.1     | A       | 2024-02-16       | I10      | 2024-02-02        |
+--------------+---------+---------+------------------+----------+-------------------+

    This assumes goal_ID and date comparison is the only relationship needed between user_steps and mods and you only want the most recent mod record after the created date. but you could change the lmit to be 2 or change the order to ascending if you want the first one after that date.

    Try it out; if it's not working let us know what's not working/meeting expectations. Ask questions if you don't understand something; I'd be happy to explain it!