查找上次更新者和上次更新日期

有人告诉我针对以下情况提出解决方案。

我需要基于“ EmployeeID”返回“最后更新日期”和“最后更新者”列。该查询必须在父表和所有子表中搜索最新条目，并返回按ID分组的列。我正在寻找一种不使用UNION ALL来连接父表和子表的解决方案，并且要解决这样一种情况，即两个不同的用户可以在不同表的同一EmployeeID（外键或主键）上输入最新记录。

例如，Jim今天更新了Employee表的EmployeeID 1，Paul也更新了EmployeeID 1的Employee_Address表。基于该EmployeeID 1，有两条记录是今天的最后更新日期，由不同用户输入。

请参阅架构和联合所有脚本，在其中我无法弄清其中的“上次更新者”部分。

CREATE TABLE EMPLOYEE
(
  EMPLOYEE_ID INT,
  NAME VARCHAR(100),
  LAST_UPDATED_BY VARCHAR(200),
  LAST_UPDATED_DT DATE
)

CREATE TABLE EMPLOYEE_AWARD
(
  AWARD_ID INT,
  EMPLOYEE_ID INT,
  AWARD_NAME VARCHAR(100),
  LAST_UPDATED_BY VARCHAR(200),
  LAST_UPDATED_DT DATE
)

CREATE TABLE EMPLOYEE_ADDRESS
(
  ADDRESS_ID INT,
  EMPLOYEE_ID INT,
  "ADDRESS" VARCHAR(100),
  LAST_UPDATED_BY VARCHAR(200),
  LAST_UPDATED_DT DATE
)


INSERT INTO EMPLOYEE
    VALUES  (1,'Paul Smith', 'GomesR', '6/22/2015'),
            (2,'Francis Jones', 'KellyR', '5/20/2013'),
            (3,'Paul Bo', 'GomesR', '11/09/2015'),
            (4,'Ed Li', 'TurnerR', '1/19/2012')


INSERT INTO EMPLOYEE_AWARD
    VALUES  (10,1,'Employee of the Month', 'GomesR', '6/22/2015'),
            (20,3,'Employee of the Month', 'KellyR', '5/20/2013'),
            (30,1,'Employee of the Quater', 'GomesR', '11/09/2015'),
            (40,3,'Employee of the Month', 'TurnerR', '1/19/2012'),
            (50,2,'Employee of the Month', 'KellyR', '11/11/2011')


INSERT INTO EMPLOYEE_ADDRESS

    VALUES  (100,1,'Address 1','GomesR', '6/25/2015'),
            (200,2,'Address 2','TurnerR', '5/28/2015'),
            (300,3,'Address 3','TurnerR', '11/20/2015'),
            (400,4,'Address 4','GomesR', '6/14/2014')



SELECT      D.EMPLOYEE_ID, Max(D.LAST_UPDATED_DT) LAST_UPDATED_DT--, D.LAST_UPDATED_BY
FROM        EMPLOYEE A

INNER JOIN  (   SELECT      EMPLOYEE_ID, LAST_UPDATED_DT, LAST_UPDATED_BY
                FROM        EMPLOYEE 
                UNION ALL
                SELECT      EMPLOYEE_ID, LAST_UPDATED_DT, LAST_UPDATED_BY
                FROM        EMPLOYEE_AWARD 
                UNION ALL
                SELECT      EMPLOYEE_ID, LAST_UPDATED_DT, LAST_UPDATED_BY
                FROM        EMPLOYEE_ADDRESS

                ) AS D

ON A.EMPLOYEE_ID = D.EMPLOYEE_ID
GROUP BY D.EMPLOYEE_ID