Need to combine two Select Statements

Question

question

Jennifer asked Oct 13, '09

Need to combine two Select Statements

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

query joins

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mrdenny commented · Oct 13, 2009 at 09:44 PM

Can you edit your question and set the code blocks and sample output as code blocks. It'll make everything much more readable.

0 ·

Answer 1 · 2009-10-13T21:46:23Z

mrdenny answered Oct 13, '09

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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question