Need to combine two Select Statements

Question

I have a table that keeps tracks of all errors logged for an application I am running. This table includes a column for the date the error occurred, a column for the time, and the error number. There can be several errors with the same error number logged. For example, the Log table can look like this:

ErrorNum Date Time LogText 245 10/12/2009 01:04:12 Error Detected 244 10/12/2009 09:34:56 Error Corrected 245 10/12/2009 15:12:26 Error Corrected 244 10/11/2009 23:45:47 Error Detected

I have a query that finds the most recent error for each error number. So my query and results from the query would look like this:

SELECT MostRecent.ErrorNum, MostRecent.ErrorDateTime, OriginalLog.LogText FROM Log OriginalLog INNER JOIN (SELECT ErrorNum, MAX( DATEADD(ms, DATEDIFF(ms, 0, LogTime), DATEADD(dd, 0, DATEDIFF(dd, 0, LogDate))) ) AS ErrorDateTime
FROM Log GROUP BY ErrorNum) AS MostRecent ON OriginalLog.ErrorNum = MostRecent.ErrorNum AND MostRecent.ErrorDateTime = DATEADD(ms, DATEDIFF(ms, 0, OriginalLog.LogTime), DATEADD(dd, 0, DATEDIFF(dd, 0, OriginalLog.LogDate)))

Results:

ErrorNum Date Time LogText 245 10/12/2009 15:12:26 Error Corrected 244 10/12/2009 09:34:56 Error Corrected

I need to also include the errormsg column that is in another table named Errors. Table Errors has a column for the ErrorNum and I need to get the ErrorMsg that matches up with each ErrorNum in the Errors table. My query right now is just from one table, so how would I add another column to my results from another table named Errors?

The Errors table would look like this:

ErrorNum ErrorMsg 244 Module1 is not connected to Server 245 Module2 is not connected to Server

And I need to have my final results to look like this:

ErrorNum Date Time LogText ErrorMsg 245 10/12/2009 15:12:26 Error Corrected Module1 is not connected to Server 244 10/12/2009 09:34:56 Error Corrected Module2 is not connected to Server

Please let me know if you have any suggestions.

Thanks, Jen

Answer 1

You should just need to add an inner join to that other table.

SELECT MostRecent.ErrorNum, MostRecent.ErrorDateTime, OriginalLog.LogText, Errors.ErrorMsg            
FROM Log OriginalLog             
INNER JOIN (SELECT ErrorNum, MAX( DATEADD(ms, DATEDIFF(ms, 0, LogTime), DATEADD(dd, 0, DATEDIFF(dd, 0, LogDate))) ) AS ErrorDateTime            
FROM Log             
GROUP BY ErrorNum) AS MostRecent ON OriginalLog.ErrorNum = MostRecent.ErrorNum             
      AND MostRecent.ErrorDateTime = DATEADD(ms, DATEDIFF(ms, 0, OriginalLog.LogTime), DATEADD(dd, 0, DATEDIFF(dd, 0, OriginalLog.LogDate)))            
INNER JOIN Errors ON MostRecent.ErrorNum = Errors.ErrorNum

Need to combine two Select Statements

Follow this question

Related Questions