Better way of writing this query

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

Better way of writing this query

Author

Topic

Webskater
Starting Member

19 Posts

Posted - 2009-03-31 : 08:49:49

I have a table called tblTAGContact which is a list of Contacts with a flag to show whether they have been 'tagged' by a department. (If one department 'tags' them - people from other departments cannot delete the contact).

I have a table called tblEmails which contains a list of Email addresses along with associated ContactIDs, LocationIDs and OrganisationsIDs. This stores email addresses that are associated with contacts, contacts at certain (organisation)locations, or organisations. There is a boolean column in here called DeFacto which says 'This email address for this contact is their main email address and should be used by default to contact them.'

I have a query which returns ... a list of contacts tagged by one (or more) departments. I only want one record back for contact and I want their default email address back - if one of their email addresses has been specifed as 'DeFacto' - if not, I want just one of their email addresses back - don't care which one.

I have this:

SELECT DISTINCT
			@DListEmailID,
			0 AS DListConID,
			(Select TOP 1 tblEmails.Email FROM tblEmails WHERE tblEmails.Active = 1 AND tblEmails.ContactID = tblTAGContact.ContactID ORDER BY DeFacto DESC) AS [Email],
			dbo.fnGetContactName(tblTAGContact.ContactID) AS MemberName,
			(Select TOP 1 tblEmails.EmailID FROM tblEmails WHERE tblEmails.Active = 1 AND tblEmails.ContactID = tblTAGContact.ContactID ORDER BY DeFacto DESC) AS [EmailID]
		FROM tblTAGContact
			INNER JOIN tblEmails ON tblTAGContact.ContactID = tblEmails.ContactID AND tblEmails.Active = 1
		WHERE tblTAGContact.DepartmentID IN(SELECT [Value] FROM dbo.SplitCSVList(@DepartmentIDs))

Although this works - it seems pretty ugly to me. Is there a better way of writing this?

Thanks for any help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-31 : 09:11:51

Are you using SQL Server 2005 or SQL Server 2000?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-31 : 09:36:52

[code]SELECT xyz,
DListConID,
Email,
MemberName,
EmailID,
FROM (
SELECT @DListEmailID AS xyz,
0 AS DListConID,
e.Email,
dbo.fnGetContactName(tblTAGContact.ContactID) AS MemberName,
e.EmailID,
ROW_NUMBER() OVER (PARTITION BY e.ContactID ORDER BY e.DeFacto DESC) AS recID
FROM tblTAGContact AS c
INNER JOIN dbo.SplitCSVList(@DepartmentIDs) AS f ON f.[Value] = c.DepartmentID
INNER JOIN tblEmails AS e ON e.ContactID = c.ContactID
AND e.Active = 1

) AS d
WHERE recID = 1[/code]

E 12°55'05.63"
N 56°04'39.26"

Webskater
Starting Member

19 Posts

Posted - 2009-03-31 : 10:17:01

SQL Server 2005

Webskater
Starting Member

19 Posts

Posted - 2009-03-31 : 10:44:18

Thanks for your reply.

I'm definitely using SQL Server 2005 but when I try to run the code you provided I get the error 'ROW_NUMBER' is not a recognized function name.

I wasn't sure if I had added a syntax error to the code you provided, so I tried something simple which (I think) should work - namely:

select row_number() over (order by DeFacto desc) as rownum, Email, EmailID from tblEmails

But this produces the same error.

I'm running:

Microsoft SQL Server Management Studio 9.00.4035.00
Microsoft Analysis Services Client Tools 2005.090.4035.00
Microsoft Data Access Components (MDAC) 2000.085.1132.00 (xpsp.080413-0852)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.13
Microsoft .NET Framework 2.0.50727.3082
Operating System 5.1.2600

Webskater
Starting Member

19 Posts

Posted - 2009-03-31 : 10:54:58

No, apparently I am not using SQL Server 2005. If I run SELECT @@VERSION it tells me I am running 2000 - which explains why it doesn't work.

I am newish to SQL Server - I presume I am using Microsoft Sequel Server 2005 Management Studio - to access a Microsoft Sequel Server 2000 database?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-03-31 : 12:34:02

Yes, SSMS 2005 can connect to a 2000 Server.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources