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

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

Tags:

2

Answers


  1. 0

    We groupby according to the cardnumber field and select those who use more than one. Then we list their id, name, cardnumber fields in ascending order. You can see the cardnumbers used more than once from here.

    SELECT ID, NAME, CARDNUMBER
FROM MY_DB
WHERE CARDNUMBER IN (
    SELECT CARDNUMBER
    FROM MY_DB
    GROUP BY CARDNUMBER
    HAVING COUNT(DISTINCT NAME) > 1
) ORDER BY CARDNUMBER ASC;
    Login or Signup to reply.
  2. 0 
    SELECT cardnumber
--   , GROUP_CONCAT(DISTINCT name SEPARATOR ',') AS names_list
FROM table 
GROUP BY 1
HAVING COUNT(DISTINCT name) > 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search