I am keeping track of people changing their names. I have three tables:
TableOne id 1 2 TableTwo Date id FirstName NameID 01/02/2016 1 TestOne 1 01/03/2017 2 TestTwo 2 01/04/2015 3 TestThree 1 TableThree Date id SecondName NameID 01/01/2016 1 TestThree 1 01/01/2018 2 TestFour 1 01/10/2014 3 TestFive 2 The NameIDs in tables 2 & 3 map to the id in table 1. Every time a person changes their first name I add a record to table 2 and every time a person changes their second name I add a record to table 3. I keep list of all their historical names.
My aim is to use a SELECT query to pull back what each person's name today is i.e. I need to SELECT the most recent pair of names for each unique id in table 1.
My end table should look like this:
NameID FirstName SecondName 1 TestOne TestFour 2 TestTwo TestFive I can get the most recent firstnames and secondnames as follows:
SELECT FirstName, MAX(Date) FROM TableTwo GROUP BY NameID; SELECT SecondName, MAX(Date) FROM TableOne GROUP BY NameID; But how can I put these 2 queries together? (Sorry if easy question - I am quite new to this and please let me know if anything unclear at all - thanks in advance!)
