I found an answer without having to worry about complicated SQL
statements - it's more about managing the tables than the SQL.
Quoting Jacob Steinberger <trefalgar@stripped>:
> 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?
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: