List:General Discussion« Previous MessageNext Message »
From:Jacob Steinberger Date:August 26 2010 2:31am
Subject:Re: Complicated SQL Query
View as plain text  
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 <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?
>
> Jacob
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1



Thread
Complicated SQL QueryJacob Steinberger26 Aug
  • Re: Complicated SQL QueryJacob Steinberger26 Aug
    • Re: Complicated SQL QueryJangita26 Aug
      • Re: Complicated SQL QueryClaudio Nanni26 Aug
  • RE: Complicated SQL QueryJerry Schwartz26 Aug
    • RE: Complicated SQL QueryJacob Steinberger26 Aug