Mysql Question posted in
The official MySQL documentation can be found here.

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
[email protected] Peter Pan
[email protected] Paul Smith

Table "registered":

client_email client_name
[email protected] James Bond
[email protected] 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.

Tags:

2

Answers


  1. 0

    perhaps wrap your query and group_concat name https://dbfiddle.uk/DSWlTERj

    DROP TABLE IF EXISTS main,registered;

create table main(client_email varchar(20), client_name varchar(20));
insert into main values
('[email protected]',   'Peter Pan'),
('[email protected]' ,   'Paul Smith'),
('aaa'  ,   'bbb');


create table registered(client_email varchar(20),   client_name varchar(20));
insert into registered values
('[email protected]',   'James Bond'),
('[email protected]' ,    'Paul Smith'),
('aaa'  ,   'ccc');

select client_email,group_concat(client_name)
from
(
SELECT client_email,client_name 
FROM `main` 
UNION 
SELECT client_email,client_name 
FROM `registered` 
) s
group by client_email;

    The sub query will dedupe on email and name and the outer query will list all the names associated with an email.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search