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 '09 at 08:49 PM in Default

Jennifer gravatar image

Jennifer
35 3 3 3

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 '09 at 09:44 PM mrdenny
(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

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 '09 at 09:46 PM

mrdenny gravatar image

mrdenny
908 3

Thank you, it worked perfectly!
Oct 14 '09 at 12:09 PM Jennifer
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x362
x111

asked: Oct 13 '09 at 08:49 PM

Seen: 2567 times

Last Updated: Oct 13 '09 at 08:49 PM