CODEWITHSUNDEEP
mysqlsql
Seba M
Seba M

Reputation: 109

Finding rows, where Name variates, but not the mail adress

I have an problem as follows, burning my mind pretty hard.

Imagine the following table (the second_id is unimportant and saying that i should use unique() is not working in my case) 

create table adressen (

id int PRIMARY KEY AUTO_INCREMENT,
second_id int,
name char(64),
email char(64)
);

INSERT INTO adressen (second_id, name, email) VALUES ('1','max','[email protected]');
INSERT INTO adressen (second_id, name, email) VALUES ('1','peter','[email protected]');
INSERT INTO adressen (second_id, name, email) VALUES ('1','emma','[email protected]');
INSERT INTO adressen (second_id, name, email) VALUES ('2','max','[email protected]');
INSERT INTO adressen (second_id, name, email) VALUES ('2','peeter','[email protected]');
INSERT INTO adressen (second_id, name, email) VALUES ('2','emma','[email protected]');
INSERT INTO adressen (second_id, name, email) VALUES ('3','maax','[email protected]');
INSERT INTO adressen (second_id, name, email) VALUES ('3','elisa','[email protected]');
INSERT INTO adressen (second_id, name, email) VALUES ('3','eemmaa','[email protected]');

As you can see, there are multiple users with the same mail, but with different names. Im trying to select all mails, where multiple names exist.

In this case it would be:

So the output shall be 1 row for each "set" of name and mail, like: 

[email protected] , emma
[email protected] , eemmaa
[email protected], max
[email protected], maxx
petertest.com, peter
petertest.com, peeter

I don't want to get the rows where mail and name are the same. Also i dont want to get a row, if there is only one entry (in this case for karl) in my table

Thanks a lot for your help!

Upvotes: 2

Views: 61

Answers (4)

isaace
isaace

Reputation: 3429

Inspired by O Jones answer and comments

This should work

SELECT DISTINCT a.mail, a.name
FROM tbl a
JOIN (
          SELECT mail
            FROM tbl
           GROUP BY mail
          HAVING COUNT(DISTINCT name) > 1
     ) b ON a.mail = b.mail
ORDER BY a.mail, a.name

Upvotes: 0

Harshil Doshi
Harshil Doshi

Reputation: 3592

Try this:

SELECT distinct mail,name FROM table 
      WHERE mail IN (SELECT mail FROM table GROUP BY mail HAVING COUNT(distinct name) > 1)
  order by mail,name;

Hope it helps!

Upvotes: 0

Barmar
Barmar

Reputation: 780929

Use a subquery that finds all the mail values with more than one different name, and join with the table itself.

SELECT DISTINCT a.mail, a.name
    FROM tbl a
    JOIN (
              SELECT mail
                FROM tbl
               GROUP BY mail
              HAVING COUNT(DISTINCT name) > 1
         ) b ON a.mail = b.mail
   ORDER BY a.mail, a.name;

DEMO

This is based on O. Jones's answer, but adds COUNT(DISTINCT name) so that rows that have the same name and email aren't counted as duplicates.

Upvotes: 2

O. Jones
O. Jones

Reputation: 108651

First get a result set containing the duplicated email addresses.

              SELECT mail
                FROM tbl
               GROUP BY mail
              HAVING COUNT(*) > 1

Then use that as a virtual table and join it to your detail table

  SELECT a.mail, a.name
    FROM tbl a
    JOIN (
              SELECT mail
                FROM tbl
               GROUP BY mail
              HAVING COUNT(*) > 1
         ) b ON a.mail = b.mail
   ORDER BY a.mail, a.name

You might try deduplicating the result set like this:

  SELECT DISTINCT a.mail, a.name
    FROM tbl a
    JOIN (
              SELECT mail
                FROM tbl
               GROUP BY mail
              HAVING COUNT(*) > 1
         ) b ON a.mail = b.mail
   ORDER BY a.mail, a.name

Upvotes: 2

Related Questions