x

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

more ▼

asked Oct 13, 2009 at 08:49 PM in Default

avatar image

Jennifer
36 3 3 5

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

Oct 13, 2009 at 09:44 PM mrdenny
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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            
more ▼

answered Oct 13, 2009 at 09:46 PM

avatar image

mrdenny
928 2 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x426
x149

asked: Oct 13, 2009 at 08:49 PM

Seen: 3176 times

Last Updated: Oct 13, 2009 at 08:49 PM

Copyright 2016 Redgate Software. Privacy Policy