tbl_user        tbl_email
________        _________
user_pk         email_pk
username        email
email_pk        tstamp
tstamp

tbl_user_hist                 tbl_email_hist
______________                ______________
user_pk                       email_pk
src_user_pk                   src_email_pk
username                      email
email_pk                      tstamp
tstamp

email_pk     src_email_pk     email                tstamp
1                   1         bob@gmail.com        2012-01-01 04:06:28
2                   1         bob@hotmail.com      2012-03-01 04:06:28
3                   1         bob@yahoo.com        2012-07-01 04:06:28

tbl_user_hist
User_pk     src_user_pk     username    email_pk      tstamp
1           1               rsmith      1             2012-01-01 04:08:28
2           1               bsmith      1             2012-02-01 04:01:28
3           1               rsmith2     1             2012-05-01 04:05:28

Username     email              username_timestamp
Rsmith       bob@gmail.com      2012-01-01 04:08:28
Bsmith       bob@hotmail.com    2012-02-01 04:01:28
Rsmith2      bob@hotmail.com    2012-05-01 04:05:28

SELECT username, email, tbl_user_hist.tstamp
FROM tbl_user_hist
INNER JOIN tbl_email_hist AS tbl_email_hist1 ON tbl_user_hist.email_pk = tbl_email_hist1.src_email_pk
WHERE src_user_pk = 1
AND tbl_email_hist1.email_pk IN (
    SELECT tbl_email_hist2.email_pk FROM tbl_email_hist2
    WHERE tbl_user_hist.email_pk = tbl_email_hist2.src_email_pk
    AND tbl_email_hist2.tstamp <= tbl_user_hist.tstamp
    ORDER BY tbl_email_hist2.tstamp DESC
    LIMIT 1
)