CODEWITHSUNDEEP
postgresqljoin
Mike S
Mike S

Reputation: 1603

Joining tables based on on most recent date

Let's say I have three tables:

I want to join the tables such that for every salaries entry there is a row with the salary value, the name associated with that row's employee_id, and the number_of_underlings value whose row's date most recently precedes the date of the salaries entry (i.e., the number of underlings the given employee would have had at the time they had the given salary).

For example, assume the following rows appear in the described tables:

id | name      employee_id | salary  |    date         employee_id | number_of_underlings |    date
---+-------   -------------+---------+------------    -------------+----------------------+------------
13 | Sally              13 |   90000 | 2017-11-29               13 |                    0 | 2017-05-13
                        13 |   95000 | 2018-01-01               13 |                    6 | 2018-02-20
                        13 |  120000 | 2018-03-01

Then the resulting join would have rows:

 name  | salary  | number_of_underlings |    date
-------+---------+----------------------+------------
 Sally |   90000 |                    0 | 2017-11-29
 Sally |   95000 |                    0 | 2018-01-01
 Sally |  120000 |                    6 | 2018-03-01

Of course, joining the employees and salaries tables is trivial, but what about the underlings table?

Upvotes: 0

Views: 43

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

One approach uses a correlated subquery:

SELECT
    e.name,
    s.salary,
    (SELECT u.number_of_underlings FROM underlings u
     WHERE e.id = u.employee_id AND s.date >= u.date
     ORDER BY u.date DESC LIMIT 1) number_of_underlings,
    s.date
FROM employees e
INNER JOIN salaries s
    ON e.id = s.employee_id

Demo

Upvotes: 1

Related Questions