List:General Discussion« Previous MessageNext Message »
From:Jacob Steinberger Date:August 26 2010 12:36am
Subject:Complicated SQL Query
View as plain text  
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

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