*** How to get UNIQUE emails from 3 different tables *** 1

[jonnywah]

I am writing a SQL stored procedure which uses a cursor to send email to various people. How can I get unique emails from EmailTable1, EmailTable2, and EmailTable3 in "one" SELECT statement?

Each person in EmailTable1 can also be in EmailTable2 or EmailTable3, etc.

Please help. Thank you in advance.

 

[vongrunt]

If email is all that matters, try:

select email from EMailTable1 UNION EMailTable2 UNION EMailTable3

 

[r937]

select email from EMailTable1
UNION
select email from EMailTable2
UNION
select email from EMailTable3

rudy

http://r937.com/

SQL Consulting

 

[dbomrrsm]

create table #tempemail (email varchar)
insert into #tempemail
select email from EMailTable1
UNION
select email from EMailTable2
UNION
select email from EMailTable3

select distinct(email)
from #tempemail

 

[r937]

DBomrrsm, UNION removes duplicates



rudy

http://r937.com/

SQL Consulting

 

[jonnywah]

I would like emails from EMailTable1, and non-duplicate emails from EMailTable2, and then non-duplicate emails from EMailTable3 in that order.

Can UNION do this? Order of emails is important in this case. Please advise. Thank you.

 

[r937]

jonnywah, i did not understand your last explanation

if you get all emails from table 1, might there be some dupe emails within table?

when you then get non-duplicate emails from EMailTable2, are you saying you want only distinct emails from EMailTable2, or only those that aren't already selected from EMailTable1? even if there are dupes withing EMailTable2?

because when it all boils down, if you just want distinct emails, UNION will do that without worrying about which tables they came from

rudy

http://r937.com/

SQL Consulting

 

[jonnywah]

Rudy,

There are no duplicate emails in EmailTable1. The only duplicates would be from EmailTable2 and EmailTable3.

Basically I want all emails from EmailTable1, and non-duplicate emails from EmailTable2 and EmailTable3. I want to be able to track the source, ie. whether the email came from EmailTable1, 2, or 3. Depending on the source, the email content will be slightly different.

I need to be able to do this in a SINGLE select statement and using a cursor to send the emails ... any suggestions would be appreciated. Thank you in advance.

 

[r937]

select email
     , min(src) as source
  from (     
       select 1 as src
            , email 
         from EMailTable1 
       UNION ALL 
       select 2
            , email 
         from EMailTable2 
       UNION ALL 
       select 3
            , email 
         from EMailTable3 
       ) as onetable
group
    by email

rudy

http://r937.com/

SQL Consulting

 

[jonnywah]

Rudy,

I can not get your code to work ...

Invalid column name 'email'.

------------------------------------------

select email
, min(src) as source
from (
select 1 as src
, NewsletterSubscriberEmail
from NewsletterSubscriber
UNION ALL
select 2
, UserEmail
from Users
UNION ALL
select 3
, UserEmail
from db2.dbo.users
) as onetable
group
by email

 

[r937]

select email
     , min(src) as source
  from (     
       select 1 as src
            , NewsletterSubscriberEmail 
                   [b]as email[/b]
         from NewsletterSubscriber 
       UNION ALL 
       select 2
            , UserEmail 
         from Users 
       UNION ALL 
       select 3
            , UserEmail 
         from db2.dbo.users 
       ) as onetable
group
    by email

rudy

http://r937.com/

SQL Consulting

 

[jonnywah]

Thank you Rudy and everyone for you help. I appreciate it.

 

[jonnywah]

Rudy,

Question - what does "min(src)" do?

Thank you in advance.

 

[r937]

it selects the lowest value of src

the src column has values 1, 2, or 3

remember this criterion: "I would like emails from EMailTable1, and non-duplicate emails from EMailTable2, and then non-duplicate emails from EMailTable3 in that order."


rudy

http://r937.com/

SQL Consulting

 

[jonnywah]

Thank you Rudy.

 

[jonnywah]

Hi Rudy,

For tracking purposes, I added "MyUserOrNewsId" to my code, and now the emails returned has duplicates. I need to get non-duplicate emails from the 3 tables and in the following order: NewsletterSubscriber, Users, and Users (in database 2). Please help.

select email, min(src) as source, MyUserOrNewsId
from (
select 1 as src, NewsletterSubscriberEmail as email, newsletterSubscriberId as MyUserOrNewsId
from NewsletterSubscriber
UNION ALL
select 2 as src, UserEmail as email, userId as MyUserOrNewsId
from Users
UNION ALL
select 3 as src, UserEmail as email, userId as MyUserOrNewsId
from db2.dbo.users
) as onetable
group by email, MyUserOrNewsId
order by email

 

[r937]

change the numbers used for the src columns, then

order by source, email

rudy

http://r937.com/

SQL Consulting

 

[jonnywah]

Rudy,

What do you mean by change the numbers used for the src columns?

I used "order by email, source" because I need the emails returned alphabetically. I can't get it to work. Duplicates are still returned. My code is below. Please help.



select email, min(src) as source, MyUserOrNewsId
from (
select 1 as src, NewsletterSubscriberEmail as email, newsletterSubscriberId as MyUserOrNewsId
from NewsletterSubscriber
UNION ALL
select 2 as src, UserEmail as email, userId as MyUserOrNewsId
from Users
UNION ALL
select 3 as src, UserEmail as email, userId as MyUserOrNewsId
from db2.dbo.users
) as onetable
group by email, MyUserOrNewsId
order by email, source

 

[r937]

okay, i think i understand now, sorry

i'm not sure it can be done easily

the idea behind the min(src) was that this would select unique emails based on your stated priority

obviously adding another field destroys that (well, it wasn't obvious to me earlier, but it is now

what you would have to do for tracking purposes is join the results back to the individual tables to pull the MyUserOrNewsId value

ugly, yes?






rudy

http://r937.com/

SQL Consulting

 

[jonnywah]

I am not good with joins. How would I join the results back to the individual tables to get the MyUserOrNewsId value?

This would be ugly but it should work...