[SOLVED] MYSQL - How to find registers with same data in a field, but different data in another field?

MYSQL - How to find registers with same data in a field, but different data in another field?

I'm trying to identify in my database which users are using other users' "IDs".

MY DB:

ID | NAME | CARDNUMBER | SHOPNUMBER 
1    JO      1234         73839
2    ANDY    9876         79487
3    KARL    5432         87399
4    MIKE    1234         53637

I need to find who is using the same CARDNUMBER with different NAMEs. Here is JO and MIKE (cardnumber 1234)

I know that "MIKE" is a fixed name - but I don't know how to find the others.

ID | NAME | CARDNUMBER | SHOPNUMBER 
1    JO      1234         73839
2    ANDY    9876         79487
3    KARL    5432         87399
4    MIKE    1234         53637
5    MIKE    5432         93890
6    MIKE    1234         34949
7    GORDON  7389         23339
8    ALEX    8390         28889
9    MIKE    7389         29800

Mike is cloning IDs and I need to find out which IDs.

(I have 50K "FakeIds" created by sellers - but the ID field is required, so these sales are using IDs that could be real customers. When the real customer pulls his data - he will pull the "fake sale" too.) = Mike is "NAME SURNAME" in DB.

I tried to SELECT but I don't know how I can't find these fields

Daniel

Solution

SELECT cardnumber
--   , GROUP_CONCAT(DISTINCT name SEPARATOR ',') AS names_list
FROM table 
GROUP BY 1
HAVING COUNT(DISTINCT name) > 1;