Help with SELECT statement with subquery? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Help with SELECT statement with subquery?

Author

Topic

brownjamesa
Starting Member

13 Posts

Posted - 2010-12-15 : 04:54:31

Hi,

I was wondering if someone could please advise me on a SQL query I am trying to create.

Example Data is all in one table:

MsgName SrvID Name
B00000000000A 1 Country - UK
B00000000000A 1 Delivered
B00000000000B 2 Country - USA
B00000000000B 2 Delivered
B00000000000C 2 Country - USA
B00000000000C 2 Spam
B00000000000D 4 Country - USA
B00000000000D 4 Virus

What I would like to achieve is the following results:

MsgName SrvID Name LastName
B00000000000A 1 Country – UK Delivered
B00000000000B 2 Country - USA Delivered
B00000000000C 2 Country - USA Spam
B00000000000D 4 Country - USA Virus

Looking on the internet, I think I need todo a sub query? So that for each record it finds, do another query based on two unique records (MsgName & SrvID) and return the Name value as a new name like "LastName".

I have already created the following queries:

The following query returns all the objects:

Select DB01.MsgName, DB01.SrvID, DB01. Name
FROM DB01

Results:
MsgName SrvID Name
B00000000000A 1 Country - UK
B00000000000A 1 Delivered
B00000000000B 2 Country - USA
B00000000000B 2 Delivered
B00000000000C 2 Country - USA
B00000000000C 2 Spam
B00000000000D 4 Country - USA
B00000000000D 4 Virus

The following query returns only Country’s:

Select DB01.MsgName, DB01.SrvID, DB01. Name
FROM DB01
Where DB01.Name LIKE ‘Country - %’

Results:
MsgName SrvID Name
B00000000000A 1 Country - UK
B00000000000B 2 Country - USA
B00000000000C 2 Country - USA
B00000000000D 4 Country - USA

The following query returns the last name for a specific msgname:

Select Top 1 DB01.Name
FROM DB01
Where DB01.MsgName = ‘B00000000000A’ AND
DB01.SrvID = ‘1’ AND
DB01.Name NOT like ‘Country - %’

Results:
MsgName SrvID Name
B00000000000A 1 Delivered

I have tried the following query with no success:

Select DB01.MsgName,
DB01.SrvID,
DB01.Name,
(
Select Top 1 DB01.Name
FROM DB01
Where DB01.MsgName = DB01.MsgName AND
DB01.SrvID = DB01.SrvID AND
DB01.Name NOT like ‘Country - %’
) as LastName
FROM DB01
Where DB01.Name LIKE ‘Country - %’

MsgName SrvID Name LastName
B00000000000A 1 Country – UK Delivered
B00000000000B 2 Country - USA Delivered
B00000000000C 2 Country - USA Delivered (Wrong!)
B00000000000D 4 Country - USA Delivered (Wrong!)

Any help you can give would be really appreciated, even if giving me some examples that I can use as a template.

Cheers
James

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2010-12-15 : 05:29:30

You almost have it. The way I look at it is this. You essentially have two tables within one, as defined by your queries, what you need to then do is self join on the key e.g.

Select t.MsgName, t.ID, t.Name + ' ' + s.Name
FROM DB01 t Inner join DB01 s
ON t.msgname = s.msgname
Where t.Name LIKE 'Country - %'
AND s.name NOT LIKE 'Country - %'

-----------

I used to think I wasn't a morning person but it never got better in the afternoon

brownjamesa
Starting Member

13 Posts

Posted - 2010-12-15 : 06:41:50

Cheers for the reply elwoos,

I think I am nearly there

Forgot to mention that sometimes I have data like below:

MsgName SrvID Name
B00000000000A 1 Country - UK
B00000000000A 1 Routred Via Server01
B00000000000A 1 Delivered

The query now returns:

MsgName SrvID Name LastName
B00000000000A 1 Country - UK Routred Via Server01
B00000000000A 1 Country - UK Delivered

How can I amend the query to include the following:

Select Top 1 DB01.Name
FROM DB01
Where DB01.MsgName = ‘B00000000000A’ AND
DB01.SrvID = ‘1’ AND
DB01.Name NOT like ‘Country - %’

Also, I am doing following joins in my query and can't 'c' to join to 't'

Select t.MessageName, t.ServerID,c.name,s.Class
FROM TblName01 t INNER JOIN TblName01 s
ON t.MessageName = s.MessageName AND t.ServerID = s.ServerID
INNER JOIN TlbClass C
ON t.Classification = C.ClassificationID

I have tried the following which didn't work:

Select t.MessageName, t.ServerID,c.name,s.Class,d.name
FROM TblName01 t INNER JOIN TblName01 s
ON t.MessageName = s.MessageName AND t.ServerID = s.ServerID
INNER JOIN TlbClass C
ON t.Classification = C.ClassificationID
INNER JOIN TlbClass D
ON s.Classification = D.ClassificationID

Cheers
James

brownjamesa
Starting Member

13 Posts

Posted - 2010-12-15 : 08:16:21

Got the join working:

FROM Message t INNER JOIN Message s
ON t.MessageName = s.MessageName AND t.ServerID = s.ServerID
INNER JOIN Classification C
ON t.Classification = C.ClassificationID
INNER JOIN Classification D
ON s.Classification = D.ClassificationID

Just need toget amend the query to get the top result back only

brownjamesa
Starting Member

13 Posts

Posted - 2010-12-15 : 11:10:30

Can anyone help me with selecting only the "TOP 1" from s.Name?

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-15 : 15:37:19

I think what you are trying to do can be accomplished using the RANK features of SQL Server 2005+.

I am not entirely sure how it all works yet because i have just started to learn how to use the features.

Hopefully someone will assist that has more experience.

But this may give a start.

Select 
	RANK() OVER (Partition By s.Class Order By t.ServerID) As 'RANK'
	t.MessageName, 
	t.ServerID,
	c.name,
	s.Class,
	d.name
FROM TblName01 t INNER JOIN TblName01 s
		ON t.MessageName = s.MessageName AND t.ServerID = s.ServerID
	INNER JOIN TlbClass C
		ON t.Classification = C.ClassificationID
	INNER JOIN TlbClass D
		ON s.Classification = D.ClassificationID

Thank You,

John

brownjamesa
Starting Member

13 Posts

Posted - 2010-12-15 : 17:13:08

Cheers for your reply John, I am using SQL 2000, I take it that RANK would work with that ?

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-15 : 18:07:09

No, it is new in SQL Server 2005 and up.

Jeff has an excellent Blog that has a good example of it's use and interesting discussion of the article.

[url]http://weblogs.sqlteam.com/jeffs/archive/2007/03/28/60146.aspx[/url]

Not sure how to handle this in SQL 2000.

Thank You,

John

brownjamesa
Starting Member

13 Posts

Posted - 2010-12-16 : 04:34:11

Cheers John,

Managed to sort it out in the end using Powershell :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources