CODEWITHSUNDEEP
phpmysqlsql
Rick
Rick

Reputation: 441

Query two tables for email addresses, list each only once

I have two tables 'memberships' and 'email_list'. Each contains an email address field. Some may be duplicated between both tables. I am trying to make a single list of records, but without displaying duplicates; each email only once.

Thinking DISTINCT is where I need to go, but not sure how to write the query, or if I need a join or not.

Any help would be appreciated!

Thanks, Rick

Upvotes: 1

Views: 144

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272106

A UNION query should do the trick:

SELECT email_address FROM memberships
UNION
SELECT email_address FROM email_list

To select multiple columns:

SELECT email_address, first_name, last_name FROM memberships
UNION
SELECT email_address, first_name, last_name FROM email_list

The above-mentioned query will return combined, distinct email-firstname-lastname tuples. To eliminate duplicate first and/or last names for a given email address, wrap the results in a GROUP BY clause:

SELECT * FROM
(
SELECT email_address, first_name, last_name FROM memberships
UNION
SELECT email_address, first_name, last_name FROM email_list
) AS temp
GROUP BY email_address

Upvotes: 8

BrianM
BrianM

Reputation: 71

To further take the example from @Salaman

SELECT email_address FROM memberships
UNION
SELECT email_address FROM email_list

You could then get the distinct values with something along the lines of this:

SELECT DISTINCT email_address FROM
(
  SELECT email_address FROM memberships
  UNION
  SELECT email_address FROM email_list
) as combined_emails

If your lists are not huge then this should suffice for ease of use.

Upvotes: 1

Related Questions