Select maximum with limits

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

Didn't realise site would strip the leading whitespace. Attached has proc in a slightly more readable format.

I wouldn't use a trigger for something that a ON UPDATE CURRENT_TIMESTAMP field could do.

You can also use the trigger for AFTER UPDATE instead of an insert trigger. (its cleaner)

I also have no idea why you're inner joining these tables togeather... Is your problem not as easy as this:

Code:

---

CREATE PROCEDURE `GetPersonData`(IN timeStamp DATETIME, person_ID INT)
BEGIN

SELECT * FROM tPerson
UNION ALL
SELECT * FROM tPersonAudit

WHERE IdPerson = person_ID
AND    etime <= timeStamp

ORDER BY etime ASC

END

---

P.S. You can keep the whitespace if you put the code between [C O D E] [/ C O D E] tags (no spaces).