Re: Complicated SQL Query
On 26/08/2010 4:31 a, Jacob Steinberger wrote: I found an answer without having to worry about complicated SQL statements - it's more about managing the tables than the SQL. Jacob I usually just turn on binary logging, that way I have a record of anything that changes in the entire database and can re-construct or roll back (by reconstructing from beginning to the time I want) from the binary log. -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- 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
Inventions come from need. Congratulations and thank you for sharing your science, Its very interesting. May be useful for other uses. Claudio On Aug 26, 2010 9:11 AM, Jangita jang...@jangita.com wrote: On 26/08/2010 4:31 a, Jacob Steinberger wrote: I found an answer without having to worry about complicated SQL statements - it's more about managing the tables than the SQL. Jacob I usually just turn on binary logging, that way I have a record of anything that changes in the entire database and can re-construct or roll back (by reconstructing from beginning to the time I want) from the binary log. -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
RE: Complicated SQL Query
-Original Message- From: Jacob Steinberger [mailto:trefal...@realitybytes.net] Sent: Wednesday, August 25, 2010 8:36 PM To: mysql@lists.mysql.com Subject: 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. [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. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- 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
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
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
