Jump to content

Complex Select Statement


Te4t0n

Recommended Posts

Hi,I have 2 tables:TBLEventRecord Input_Time as DateTime Mode_ID as IntegerTBLMode Mode_ID Mode_DescriptionI need to get all the rows from TBLEventRecord between two dates (StartTime, FinishTime) and then find the Min of the Input_Time in that range and use it's Mode_ID to get it's Mode_Decription.so far I have:SELECT MIN(Input_Time) AS Input_Time FROM (SELECT TBLMode.Mode_Description, TBLEventRecord.Input_Time FROM TBLEventRecord INNER JOIN TBLMode ON TBLEventRecord.Mode_ID = TBLMode.Mode_ID WHERE (TBLEventRecord.Input_Time >= @StartTime) AND (TBLEventRecord.Input_Time <= @FinishTime)) AS TempModesTable Which gets me the Min Input_Time from a given range, which is great so far. But i now need to get it's Mode_ID out of TBLEventRecord and then get the Mode_Description out of TBLMode.I'm thinking along the lines of now using WHERE IN to get the ID and again for the Mode Description. Is this the best way or is there a short SQL Statement?

Link to comment
Share on other sites

I have the Mode_ID using Where INSELECT Mode_IDFROM TBLEventRecordWHERE (Input_Time IN (SELECT MIN(Input_Time) AS Input_Time FROM (SELECT TBLMode.Mode_Description, TBLEventRecord_1.Input_Time FROM TBLEventRecord AS TBLEventRecord_1 INNER JOIN TBLMode ON TBLEventRecord_1.Mode_ID = TBLMode.Mode_ID WHERE (TBLEventRecord_1.Input_Time >= @StartTime) AND (TBLEventRecord_1.Input_Time <= @FinishTime)) AS TempModesTable))I'll do the same for Mode_Description:WHERE (Mode_ID IN (SELECT Mode_ID FROM TBLEventRecord WHERE (Input_Time IN (SELECT MIN(Input_Time) AS Input_Time FROM (SELECT TBLMode_1.Mode_Description, TBLEventRecord_1.Input_Time FROM TBLEventRecord AS TBLEventRecord_1 INNER JOIN TBLMode AS TBLMode_1 ON TBLEventRecord_1.Mode_ID = TBLMode_1.Mode_ID WHERE (TBLEventRecord_1.Input_Time >= @StartTime) AND (TBLEventRecord_1.Input_Time <= @FinishTime)) AS TempModesTable))))this works, and I;m pretty happy with it now i look back. :) i would just like confirmation this is the best way. thks.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...