Distinct ++

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.

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

Quote:

---

Originally Posted by Nightwalker83

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.

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

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

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