Mysql，一对多的关系，需要最新的

Question

It's a long time since I've worked with MySQL and I'm learning fast, with a lot of help from stackoverflow along the way. This one has me stumped though. I'm certain I've done something similar in the past but despite trying a few things I haven't achieved it yet. I suspect last time I had to utilise PHP code to ignore duplicate rows but I'm not so sure.

I am working with two tables. Table 1 has a unique record for every year, and a reference to a dept. Table 2 stores the head of each dept, and the year they were appointed.

Table 1          Table 2
Year | Dept      Dept | Head       | Since Year
1982 | 1         1    | Hutchinson | 1979
1983 | 2         1    | Holroyd    | 1983
1984 | 2         1    | Farrey     | 1987
1985 | 2         2    | Mainwaring | 1983
1986 | 1         2    | Blanche    | 1985
1987 | 2         3    | Sunt       | 1986
1988 | 3

What I need is a query that returns the person in charge of the referenced dept at the year in question. Like this:

1982 | 1 | Hutchinson
1983 | 2 | Mainwaring
1984 | 2 | Mainwaring
1985 | 2 | Blanche
1986 | 1 | Holroyd
1987 | 2 | Blanche
1988 | 3 | Sunt

MySQL has moved on a fair bit since I last used it. Sub-queries weren't a thing back then and I'm thinking they might help here? Any advice appreciated.

Answer 1

You don't have to use subquery.

Just select valid Heads twice and then discard those where there exists Head appointed later.

SELECT t1.*,t2.Head 
FROM Table1 t1
JOIN Table2 t2 ON t1.Dept = t2.Dept AND t1.Year >= t2.`Since Year`
LEFT JOIN Table2 t22 ON t2.Dept = t22.Dept AND t1.Year >= t22.`Since Year` AND t2.`Since Year` < t22.`Since Year` 
WHERE t22.Head IS NULL

t2.`Since Year` < t22.`Since Year` find Heads appointed later than current Head

WHERE t22.Head IS NULL select only Heads without successor

Demo