Querying all data from a table joining ONLY the latest record from a junction table

Question

I'm working with a database structure similar to this one: http://dev.mysql.com/doc/employee/en/sakila-structure.html

Table: employees

Table with information about each employee.

+---------+----------+
| emp_no* | emp_name |
+---------+----------+
| emp1    | John     |
| emp2    | Mike     |
| emp3    | Rob      |
| emp4    | Kim      |
+---------+----------+

Table: departments

Table with information about the departments of the company.

+----------+-----------+
| dept_no* | dept_name |
+----------+-----------+
|       1  | Dep 1     |
|       2  | Dep 2     |
|       3  | Dep 3     |
|       4  | Dep 4     |
|       5  | Dep 5     |
+----------+-----------+

JUNCTION TABLE: emp_dept

primary key: [ emp_no, from_date ]

Table to keep track of the departments where an employee had worked before or is working right now.

+---------+----------+------------+------------+
| emp_no* | dept_no  | from_date* |  to_date   |
+---------+----------+------------+------------+
| emp1    |        1 | 2010-01-01 | 2010-12-31 |
| emp2    |        2 | 2010-01-01 | 2013-10-31 |
| emp1    |        4 | 2010-12-31 | 2012-06-14 |
| emp3    |        3 | 2010-01-01 | 2011-08-14 |
| emp4    |        1 | 2010-01-01 | 2014-11-14 |
| emp2    |        5 | 2013-10-31 | 2014-11-14 |
| emp1    |        3 | 2012-06-14 | 2014-11-17 |
| emp3    |        1 | 2011-08-14 | 2013-07-20 |
| emp3    |        4 | 2013-07-20 | 2014-11-14 |
+---------+----------+------------+------------+

THE EXPECTED TABLE:

¿How could I join only the latest record for each employee from the junction table (emp_dept) to my employee table and get a table like the one below?

+---------+----------+--------+
| emp_no* | emp_name | dep_no |
+---------+----------+--------+
| emp1    | John     |      3 |
| emp2    | Mike     |      5 |
| emp3    | Rob      |      4 |
| emp4    | Kim      |      1 |
+---------+----------+--------+

`emp_no` in Employees table is different than `emp_no` in `emp_dept` table. How are you supposed to link these two tables — CSharper, Nov 17 '14 at 18:17
ups... I just corrected the tables. Thanks for the observation — rtribaldos, Nov 17 '14 at 19:40

radar · Answer 1 · 2014-11-17T20:06:53.093

0

you can get the maximum date in a subquery and join with it.

looks like you have a typo in the emp_dept table entries, the emp_no is not matching with employees table.

In case a employee is currently working in deparment, does to_date will be NULL?

In such as case, you need to handle it in the sub query.

SELECT e.emp_no, e.emp_name, ED.dept_no
FROM 
(
   SELECT emp_no, max(to_date) as maxDate
   FROM emp_dept 
   group by emp_no)T
JOIN employee e
ON T.emp_no = e.emp_no
JOIN emp_dept ED
on T.maxDate = ED.t_date
AND ED.emp_no = T.emp_no

edited Nov 17 '14 at 20:06

answered Nov 17 '14 at 18:19

radar

13,270
2
25
33

YOu need a group by in the derived table – HLGEM Nov 17 '14 at 18:42
I'm getting this error message: Error Code: 1054. Unknown column 'ED.maxDate' in 'on clause' – rtribaldos Nov 17 '14 at 20:05
@swordf1zh, it should be T.maxDate, can you try now? – radar Nov 17 '14 at 20:07
@RADAR it is working now, but using this approach is more complex to include data from more than one junction table. I'm using another set of similar tables for managing 'roles' and would also like to include role info to the result table. – rtribaldos Nov 17 '14 at 20:33

score 0 · Answer 2 · answered Nov 17 '14 at 18:19

Create a query that only delivers the last Dept for each Employee, then add that to your main query in a JOIN or a CROSS APPLY

... tables with joins etc ...
CROSS APPLY
(
   SELECT TOP 1 employee , dept_no FROM emp_dept 
   WHERE employee = !EMP FROM MAIN TABLE!
   ORDER BY to_date DESC
) AS last_dept

where !EMP FROM MAIN TABLE! is your employee value from the tables before the CROSS APPLY (Post your full query so far, for a more complete answer)

score 0 · Accepted Answer · answered Nov 17 '14 at 18:20

Assuming emp_dept.Emp_no is the relation between employees.Emp_no

Select * from 
employees e
join emp_dept ed on e.emp_no = ed.emp_no 
                    and from_date = (Select Max(from_date) 
                       from emp_dept ed2 where ed2.emp_no = e.emp_no)

Querying all data from a table joining ONLY the latest record from a junction table

3 Answers3