Complicated SQL Query

I have a requirement to keep track of a set of data, and all changes =20
that might occur. In order to do this, for each field of the data set, =20
I've created a table that keeps track of the version, the value, the =20
time the change was made, and a linking number that links all the =20
different tables back to a single record. I'm assuming, hoping, and =20
believe this is a very common setup.

What I'm having trouble with is queries that aren't nested =20
sub-selects, or joins that won't show NULL data. For example ...

select rsi.value, rsi.record_id, ssn.value as serviceseqnum, esn.value =20
as eventseqnum from record_set_id as rsi LEFT JOIN serviceseqnum as =20
ssn ON rsi.record_id =3D ssn.record_id LEFT JOIN eventseqnum as esn ON =20
ssn.record_id =3D esn.record_id

Will join the tables, but doesn't take the version information into =20
consideration. If I add a where to include the maximum version, to get =20
the most recent value, it won't show anything if one of the values =20
happens to be NULL.

Using sub-selects generally causes long query time ...

select rsi.value, rsi.record_id ( select value from serviceseqnum =20
where record_id =3D rsi.record and version =3D ( select max(version) from =
=20
serviceseqnum where record_id =3D rsi.record_id ) ) from record_set_id ) =20
from record_set_id as rsi

.... especially when trying to get a dozen values strung together so =20
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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Complicated SQL Query

I found an answer without having to worry about complicated SQL =20
statements - it's more about managing the tables than the SQL.

Jacob

Quoting Jacob Steinberger :

> 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 =3D ssn.record_id LEFT JOIN eventseqnum as esn ON
> ssn.record_id =3D 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 =3D rsi.record and version =3D ( select max(version) from
> serviceseqnum where record_id =3D 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 o=
f?
>
> Jacob
>
>
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Dtrefalgar@realitybytes. net




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

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: jangita@yahoo.com
Skype: jangita | GTalk: jangita.nyagudi@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Complicated SQL Query

--0015175cd1da3d8f4e048eb546fa
Content-Type: text/plain; charset=ISO-8859-1

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" 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: jangita@yahoo.com
> Skype: jangita | GTalk: jangita.nyagudi@gmail.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
>

--0015175cd1da3d8f4e048eb546fa--

RE: Complicated SQL Query

>-----Original Message-----
>From: Jacob Steinberger [mailto:trefalgar@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: jerry@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=gcdmg-mysql-2@m.gmane.org

RE: Complicated SQL Query

Quoting Jerry Schwartz :
>> -----Original Message-----
>> From: Jacob Steinberger [mailto:trefalgar@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 eac=
h
> individual field? Or would it be sufficient to have version tracking for e=
ach
> 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 form=
er,
> then I think your design might be the only way to go.

Separate version tracking for each individual field. This is due to =20
all fields have a possibility of being edited/changed, but more than =20
likely only a few fields will be regularly updated.

The end idea that was given to me, which is quite easy, is to maintain =20
two methods. Use the multi-table method to track all the historical =20
changes, then use a single table with all the columns to base searches =20
/ processing off of. When needing to do an update, the only difference =20
is you update both locations instead of relying on a weird JOIN or =20
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=3Dgcdmg-mysql-2@m.gmane.o rg