Distinct ++

[systech44]

Hello,

I have a situation with SQL query. I have the following 2 tables.

Table 1
Name Email
aa [email protected]
bb [email protected]
cc [email protected]
dd [email protected]
ee [email protected]
bb [email protected]

Table 2
Name Email
bb [email protected]
dd [email protected]

Now I wish to eliminate the common emails of Table 2 from Table 1 and send email to the rest of the person. This means emails are going to aa, bb (last person), cc and ee. I am trying to use DISTINCT keyword with email and getting success. But along with the email I want to get the names of the corresponding persons. I wish to get distinct emails and their corresponding names and send a common emails to each of them. I need to know the sql query to overcome the situation.

Please help. Thank you so much.

[Nightwalker83]

Do you mean the exact email address or just part of the email address such as: gmail or com, etc?

[systech44]

Do you mean the exact email address or just part of the email address such as: gmail or com, etc?

I wish to get the following result.

Name Email
aa [email protected]
cc [email protected]
ee [email protected]
bb [email protected]

Please help.

[Pradeep1210]

select email from table1
union select email from table2
group by email
having count(email) = 1

[penagate]

select name, email from table1 where table1.email not in (select email from table2);

[penagate]

Better:

select distinct name, email from table1
left join table2 r using (name, email)
where r.name is null


Of course, this all assumes you're using MySQL. If you're using T-SQL, it is even simpler:

select name, email from table1
minus
select name, email from table2