select distinct / union - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

select distinct / union

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-09-10 : 03:46:44

I have the following query, its for selecting emails to mail out to. Occasionally an emailaddress will be in both tables and I don't want to email them twice.

How can I use 'distinct' to make sure I don't get any duplicates? Or is there a different approach?

Thanks alot

mike123

CREATE PROCEDURE select_mailingList

AS SET NOCOUNT ON
select emailaddress from tblUserDetails where mailinglist ='1' AND emailAddress NOT IN (SELECT emailAddress from tblEmail_Unsubscribe)
union
select email from tblEmailList WHERE email NOT IN (SELECT emailAddress from tblEmail_Unsubscribe)

GO

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-10 : 03:49:12

use union all i think it removes duplicate values, try it out

or you can use joins and apply the distinct keyword on emailaddress

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-09-10 : 04:01:27

That's the wrong way around. just use union...

just do

select emailaddress from tblUserDetails where mailinglist='1' 
union select emailaddress from tblEmail_Unsubscruibe

from BOL

quote:
UNION

Specifies that multiple result sets are to be combined and returned as a single result set.

ALL

Incorporates all rows into the results, including duplicates. If not specified, duplicate rows are removed.

(Of course, that's assuming you don't actually already have duplicates in either of the tables)

Hope that helps

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-10 : 04:08:46

thanks for the correction

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-10 : 04:15:15

quote:
(Of course, that's assuming you don't actually already have duplicates in either of the tables)

The UNION will take care of that too.

create table #dups(spamlist varchar(255))

insert #dups values('bugs@microsoft.com')
insert #dups values('bugs@microsoft.com')

select spamlist from #dups
union
select spamlist from #dups

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-09-10 : 04:31:38

hey guys .. forgot to mention the table you see is the spamlist to not email to, thats why its there. not for duplicate purposes..

looks like this sproc is what it was supposed to be afterall :)

I do have one problem tho. I have some inconsistent data it looks like perhaps just a few fields. My email application was errored out due to an invalid email recipient somewhere in the data brought back.

Is there anyway I can remove the invalid email addresses in my email fields? I could write a vb app, but that would be a hassle.

Im wondering if theres a way with just T-SQL ??

thanks again! ..

mike123

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-10 : 04:37:11

you can rule them out before you insert them into the tables,
use an instead of trigger on the table to check if the email add is invalid before the insert is committed...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-10 : 04:41:43

i'd use regex to deal with incorrect emails:
check this out, comes in handy.

http://www.regexlib.com/DisplayPatterns.aspx

you can do it in sql or in your app. i guess the easiest way is to apply regex on the field entering the mail. quickest and cleanest.

Go with the flow & have fun! Else fight the flow

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-09-10 : 04:44:42

thanks spirit1!

I have validation on my website input now, just some old records slipped in there. If I can filter out and delete the current bad records that would be the best solution as no more will be allowed in.

If anyone knows how to do this in sql, that would be a great help.

Thanks again!

mike123

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-10 : 04:49:20

[url]http://www.sqlteam.com/item.asp?ItemID=13947[/url]

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources