>> Then I should make another table to hold all the events that occur in 
>> all the experiments.
>>
>> ExperimentID,EventDateTime,EventID
>> 0,blah,1
>> 0,blah,0
>> 0,blah,2
>> 0,blah,1
>> 1,blah,1
>> ...
>>
>> OK.  That all seems quite basic to me so far and seems the best way to 
>> organise things.
>>
>> Now, is it possible to do a search using SQL that does something like 
>> this: Find all the times that an experiment has gone over temperature 
>> while switched on (ie after a switched on event but not after a 
>> switched off event)?  This is beyond my basic knowledge how I would do 
>> such a thing without writing some code to manually search the results 
>> and checking times.
> 
> Mmm, difficult to see, the Dark Side is...
> 
> I would suggest a 3-way self-join of the event table with itself. Find 
> all occurrances of event #0, event #1 and event #2 in the same 
> experiment, with constraints on the time orderings therein.
> 
> If that makes sense?

SELECT *
FROM EventRecord AS X, EventRecord AS Y, EventRecord AS Z
WHERE
   X.ExperimentID = Y.ExperimentID AND
   Y.ExperimentID = Z.ExperimentID AND
   X.EventID = 0 AND
   Y.EventID = 2 AND
   Z.EventID = 1 AND
   X.EventDateTime < Y.EventDateTime AND
   Y.EventDateTime < Z.EventDateTime;

Or similar. (It's been a while since I did SQL...)

Post a reply to this message