*** UNIQUE emails from different tables NOT working ***

[jonnywah]

I would like emails from NewsletterSubscriber, and non-duplicate emails from Users, and then non-duplicate emails from Users (in database 2) in that order.


For tracking purposes, I am using "MyUserOrNewsId" and even with the use of UNION, the emails returned has duplicates. 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

 

[rsinj]

try adding:

having src = min(src)

in the line after the group by.

I haven't got time to set up your scenario to try it, nor can I think straight at 1AM, but it is something easy to try.

 

[jonnywah]

I added "having src = min(src)" after the group by cause but get an error:

Column 'onetable.src' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

 

[rsinj]

ok, then

having source = min(source)

 

[jonnywah]

Didn't work ... error:

Invalid column name 'source'