I have a requirement to keep track of a set of data, and all changes
that might occur. In order to do this, for each field of the data set,
I've created a table that keeps track of the version, the value, the
time the change was made, and a linking number that links all the
different tables back to a single record. I'm assuming, hoping, and
believe this is a very common setup.
What I'm having trouble with is queries that aren't nested
sub-selects, or joins that won't show NULL data. For example ...
select rsi.value, rsi.record_id, ssn.value as serviceseqnum, esn.value
as eventseqnum from record_set_id as rsi LEFT JOIN serviceseqnum as
ssn ON rsi.record_id = ssn.record_id LEFT JOIN eventseqnum as esn ON
ssn.record_id = esn.record_id
Will join the tables, but doesn't take the version information into
consideration. If I add a where to include the maximum version, to get
the most recent value, it won't show anything if one of the values
happens to be NULL.
Using sub-selects generally causes long query time ...
select rsi.value, rsi.record_id ( select value from serviceseqnum
where record_id = rsi.record and version = ( select max(version) from
serviceseqnum where record_id = rsi.record_id ) ) from record_set_id )
from record_set_id as rsi
... especially when trying to get a dozen values strung together so
they appear as one record.
Is there a better way to handle these queries that I'm just not thinking of?
Jacob
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
