RE: Complicated SQL Query
Quoting Jerry Schwartz je...@gii.co.jp: -Original Message- From: Jacob Steinberger [mailto:trefal...@realitybytes.net] 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. [JS] That's a lot of tables. Do you need separate version tracking for each individual field? Or would it be sufficient to have version tracking for each row, with a list of fields modified and their before values? If the latter, then you can get by with a lot less complexity; if the former, then I think your design might be the only way to go. Separate version tracking for each individual field. This is due to all fields have a possibility of being edited/changed, but more than likely only a few fields will be regularly updated. The end idea that was given to me, which is quite easy, is to maintain two methods. Use the multi-table method to track all the historical changes, then use a single table with all the columns to base searches / processing off of. When needing to do an update, the only difference is you update both locations instead of relying on a weird JOIN or nested-sub-select view. Cheers, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Complicated SQL Query
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
Re: Complicated SQL Query
I found an answer without having to worry about complicated SQL statements - it's more about managing the tables than the SQL. Jacob Quoting Jacob Steinberger trefal...@realitybytes.net: 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=trefal...@realitybytes.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
