Search code examples
mysql

Query multiple tables to extract unique results only

I've been googling this for a while now but found no solution. OK I have 2 tables that look like this: Table "main":

client_email client_name
bla@email.com Peter Pan
flux@xyz.com Paul Smith

Table "registered":

client_email client_name
yop@email.com James Bond
flux@xyz.com Paul Smith

Now some clients from table "main" will be in table "registered" but not all of them. Also there may be some clients who are in table "registered" but not in table "main". And lastly, the same client (email) may be multiple times in table main but only once in table registered.

Now what I want to do is a query that finds me all UNIQUE emails from both tables and the corresponding names (I don't care from which table). I have tried:

SELECT client_email,client_name 
FROM `main` 
UNION 
SELECT client_email,client_name 
FROM `registered` 
ORDER BY client_email ASC;

but this yielded duplicate emails as it not just looks for unique emails but entries where BOTH the email and client name are unique. So having the client name spelled just slightly different in one entry (e.g., with a hyphen or accent, etc.) will result in that email being collected more than once. I can get it to work on a single table using the GROUP BY(client_email) but I can't figure out how to collect all unique emails from 2 or more tables.

Desired outcome. Based on the sample data above, I would like the following result of the query:

| client_email | client_name    |
| ------------ | -------------- |
| bla@email.com| Peter Pan      |
| yop@email.com| James Bond     |
| flux@xyz.com | Paul Smith     |
Solution

  • You can group by client_email and take any client_name, if there several clients with this email.
    Possible use UNION ... segment of subquery several times, how many tables do you have.

    See example

    select client_email,min(client_name) client_name,count(*) name_count
from(
   SELECT client_email,client_name 
   FROM `main` 
   UNION 
   SELECT client_email,client_name 
   FROM `registered` 
    -- union other tables
   UNION
   SELECT client_email,client_name 
   FROM `registered_2` 
)subU
group by client_email
ORDER BY client_email ASC;

    If you can see client_name doubles for email, add column count(). And HAVING count()>1 for filter doubles.