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 |
+---------+----------+--------+

Answer 1

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)

Answer 2

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

Answer 3

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)