Hi, I have not posted before so apologies if this is not set out the way expected.

I have a primary table tPerson containing personal details. tPerson has relationships with may other tables. tPerson.IdPerson is the primary key. Any time a record in tPerson is updated, a trigger puts the old record into tPersonAudit like this:

INSERT INTO tPersonAudit SELECT * FROM tPerson WHERE IdPerson = OLD.IdPerson;.

Trigger also makes sure IdPerson cannot be changed so we are sure that every record in tPersonAudit is related to correct tPerson record. Trigger also does:

SET NEW.eTime = NOW();

which means tPerson and tPersonAudit combined have a full history of what the records in tPerson have been. I want to query them to get the tPerson record as it looked at some time in the past which means finding the maximum eTime, under a limit for a specific IdPerson. I have solved it with the following procedure but it would be much neater if I could do it with a view as I have many such pairs tables that I want to query together with joins. Any insight would be much appreciated!

Proc is as follows:

-- get the tPerson table as it was at timeStamp
CREATE PROCEDURE `GetPersonData`(IN timeStamp DATETIME)
BEGIN

SELECT
tempPerson.*
FROM
(
SELECT
TEMP_PERSON_TABLE.*
FROM
(
SELECT
tPerson.*
FROM
tPerson
WHERE
eTime <= timeStamp
UNION
SELECT
tPersonAudit.*
FROM
tPersonAudit
WHERE
eTime <= timeStamp
) AS TEMP_PERSON_TABLE
INNER JOIN
(
SELECT
IdPerson, MAX(eTime) AS eTimeMax
FROM
(
SELECT
tPerson.*
FROM
tPerson
WHERE
eTime <= timeStamp
UNION
SELECT
tPersonAudit.*
FROM
tPersonAudit
WHERE
eTime <= timeStamp
) AS UNION_PERSON_TABLE
GROUP BY IdPerson
) AS MAX_PERSON_TABLE
ON
MAX_PERSON_TABLE.eTimeMax = TEMP_PERSON_TABLE.eTime
AND
MAX_PERSON_TABLE.IdPerson = TEMP_PERSON_TABLE.IdPerson
) AS tempPerson

END