SQL query statement question [Resolved]

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL query statement question [Resolved]

Author

Topic

brownjamesa
Starting Member

13 Posts

Posted - 2011-01-20 : 16:52:06

Hi all,

Back again with another SQL query statement question.

I have the attached data in a single table (message) which from a SMTP server.

Each entry in the table has a unique 'MessageName & ServerID', but will have multiple entries due to the SMTP server classifying the message based on different rule.

I have a requirement to extract the 'Description' data from entries which have the 'Cat' as 'Monitor', but only for entries that have a 'Cat' as 'Block'.

Please see attached picture for the results I want to end up with.

I have managed this using Powershell but takes a long time due to the number of entries, but can't seem to get it right in SQL, which I know will be faster.

I think I am just missing the logic in the 'Having' statement

SELECT 	* 
From 	(
		Select 	'Monitor' as Cat, TimeLogged,MessageName,ServerID,Subject,Description,Classification
		From 	Message
		Where 	TimeLogged >= '2011-01-20 20:26' AND
			Classification = '142' --Monitor Bad Language

		UNION ALL

		Select 	'Block' as Cat, TimeLogged,MessageName,ServerID,Subject,Description,Classification
		From 	Message
		Where 	TimeLogged >= '2011-01-20 20:26' AND
			Classification = '143' --Blocked Bad Language
	) as tmp
Group By MessageName,Cat,TimeLogged,ServerID,Subject,Description,Classification
Having (Cat = 'Block')
Order By MessageName

Below are the current results:

Can anyone shed any light?

Cheers
James

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-20 : 19:57:06

I think your strategy might be off. You want 5 columns of identical data, and 1 column from each row where they match..so this would be better served by a join. Can't test because I didn't enter your data, but I think something like this would work..


		

SELECT 	 a.Cat
		,a.TimeLogged
		,a.MessageName
		,a.ServerID
		,a.Subject
		,a.MessageName AS  DescriptionBlock
		,b.MessageName AS  DescriptionMonitor
From [Message] a inner join [Message] b
	ON	a.TimeLogged = b.TimeLogged AND
		a.ServerID = b.ServerID AND
		a.Subject = b.Subject AND
		a.MessageName = b.MessageName
Where 	a.TimeLogged >= '2011-01-20 20:26' AND
		a.Classification = '142'

Poor planning on your part does not constitute an emergency on my part.

brownjamesa
Starting Member

13 Posts

Posted - 2011-01-21 : 01:39:59

Cheers dataguru1971,

Still not right?


SELECT 	 	a.TimeLogged
		,a.MessageName
		,a.ServerID
		,a.Subject
		,a.Description AS  DescriptionBlock
		,b.Description AS  DescriptionMonitor

From 		[Message] a inner join [Message] b
		ON a.TimeLogged = b.TimeLogged AND
		a.ServerID = b.ServerID AND
		a.Subject = b.Subject AND
		a.Description = b.Description

Where 		a.TimeLogged >= '2011-01-20 20:26' AND
		a.Classification = '143'

Restuls are shows as below:

brownjamesa
Starting Member

13 Posts

Posted - 2011-01-21 : 01:54:58

dataguru1971,

Cheers for pointing me in the right direction, I have managed to get the data I wanted:

Code:


SELECT 	 	a.TimeLogged
		,a.MessageName as aMess
		,b.MessageName as bMess
		,a.ServerID
		,a.Subject
		,a.Classification AS aClass
		,b.Classification AS bClass
		,a.Description AS  DescriptionBlock
		,b.Description AS  DescriptionMonitor

From 		[Message] a inner join [Message] b
		ON a.MessageName = b.MessageName AND
		a.ServerID = b.ServerID

Where 		a.TimeLogged >= '2011-01-20 20:26' AND
		a.Classification = '143' AND
		b.Classification = '142' AND
		a.MessageName = b.MessageName AND
		a.ServerID = b.ServerID

Screenshot:

brownjamesa
Starting Member

13 Posts

Posted - 2011-01-21 : 01:58:36

dataguru1971,

Could I ask one for favour?

When doing the following in SQL, what it is doing?

SELECT a.TimeLogged
,a.MessageName as aMess
,b.MessageName as bMess

As in, by select "a.timelogged", it is selecting that entry and storing in the memory under a table called "a"?

a.timelogged
a.messagename
b.messagename

Just wanted to understand how it works really.

Cheers
James

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-21 : 06:36:12

Happy to help, sorry I missed your responses

this explains it pretty simply enough http://en.wikipedia.org/wiki/Join_%28SQL%29

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources