sql - Create a view using latest address record -

i'm trying create view joins 2 tables , returns relevant address details of particular date.

i have 2 tables:

employee:

id (primary key) name 

employeeaddress:

id (pk), (fk employee.id) datevalidfrom (pk) addressline1 addressline2 postcode 

how join 2 employee details , address of date?

one way use query finds latest datevalidfrom each id , use derived table join other tables with, this:

select * employee e join employeeaddress ea on e.id = ea.id join (select id, max(datevalidfrom) max_date employeeaddress group id) ea2   on ea.id = ea2.id , ea.datevalidfrom = ea2.max_date 

by joining max(datevalidfrom) in derived table limit rest of query (that rows) latest.

and turn view:

create view employee_latest_address select e.id, e.name, ea.datevalidfrom, ea.addressline1, ea.addressline2, ea.postcode  employee e join employeeaddress ea on e.id = ea.id join (select id, max(datevalidfrom) max_date employeeaddress group id) ea2   on ea.id = ea2.id , ea.datevalidfrom = ea2.max_date