MySQL: Find most recent row in one table for each row in another table containing a specific date

Vote count:

0

Having a hard time creating a mysql query to do the following.

We have a list of staff with columns as follows:

eg: employee table
name    em_date   (other cols)
Fred    1-1-1960
Fred    1-1-1970
Fred    1-1-1980
John    1-1-1960
John    1-1-1990

We have a second table containing family details:

Partners table
name       marriage_date     partner_name     (other cols)
Fred       1-1-1959          Sue
Fred       1-1-1969          Marg
John       1-1-1985          Joan

I'm trying to generate a table containing: name, employment_date, marriage_date, partner_name

Some staff have been employed multiple times (think contractors) so they have multiple employment dates. Some of them have also remarried and so have different partners at different times (and/or may not have been married at some time)

I need the output to show the partner at the time of their employment.

The output should be:

name       em_date      mar_date     partner_name
Fred       1-1-1960     1-1-1959     Sue
Fred       1-1-1970     1-1-1969     Marg
Fred       1-1-1980     1-1-1969     Marg
John       1-1-1960     null         null
John       1-1-1990     1-1-1985     Joan

I was certain I could use the SQL from the link below, but alas I am unable to resolve it http://ift.tt/1uYpCLg

Any pointers greatly appreciated - I'd include what I've tried but I've tried too much and it's just not working. I either get the cartesian product or a single row.

There are keys on name and the 2 dates, but no others (ie:there is no id linking the employee to their partner at the time of their employment [think importing old data after the fact...]), hence I have to search by name and match the nearest partner based on the calculation:

min(employment_date - marriage date)

Many thanks in advance.

asked 2 mins ago

sparx27

1

MySQL: Find most recent row in one table for each row in another table containing a specific date