CODEWITHSUNDEEP
mysql
Sahbaj Uddin
Sahbaj Uddin

Reputation: 41

List all duplicate name with different id in MySQL

I have a table of more than 50k+ data with lots of duplicate. Need to list all the fields with duplicate names.

Here is my table

id | name   | comid | did
--------------------------
1  | sahbaj | 1     | 1
---------------------------
2  | sahbaj | 2     | 2
---------------------------
3  | rafi   | 3     | 3
---------------------------
4  | zaaef  | 4     | 34
---------------------------
5  | promi  | 5     | 5
---------------------------
6  | sanu   | 6     | 5
---------------------------
7  | rafi   | 7     | 3
---------------------------
8  | sd     | 43    | 8
---------------------------
9  | sd     | 6     | 9

My target output

    id |  name  | did
   --------------------------
    1  | sahbaj | 1
   --------------------------
    2  | sahbaj | 2
   --------------------------
    8  | sd     | 8
   --------------------------
    9  | sd     | 9
   --------------------------

I have tried with this MySQL query

    SELECT id,u.name,  u.did
    FROM test u
    INNER JOIN (
    SELECT NAME,did,  COUNT(*)
    FROM test
    GROUP BY NAME
    HAVING COUNT(*) > 1) temp
    ON (temp.name = u.name)
    ORDER BY NAME

Which gives me following output

    id | name   | did
   --------------------------
    3  | rafi   | 3
   --------------------------
    7  | rafi   | 3
   --------------------------
    1  | sahbaj | 1
   --------------------------
    2  | sahbaj | 2
   --------------------------
    8  | sd     | 8
   --------------------------
    9  | sd     | 9
   --------------------------

Also tried following 

    SELECT id, name, did
    FROM test t
    WHERE EXISTS (SELECT 1
    FROM test t2
    WHERE t2.name = t.name
    AND t2.did  t.did)

it gives the target output but if i run this in actual table then it goes for infinite time

Upvotes: 2

Views: 697

Answers (3)

Sahbaj Uddin
Sahbaj Uddin

Reputation: 41

I found the solution as follows

First i created a view in MySQL

 CREATE
 VIEW duplicates 
 AS
 (SELECT u.id,u.name,  u.did
  FROM test u
  INNER JOIN (
   SELECT NAME,did,  COUNT(*)
     FROM test
     GROUP BY NAME
     HAVING COUNT(*) > 1) temp
     ON (temp.name = u.name)
     ORDER BY name);

Then run this query

  SELECT p.id, p.name,p.did 
  FROM test AS p 
  INNER JOIN duplicates AS d ON (d.name=p.name AND d.did!=p.did);

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You should not use did in the inner query but only name 

SELECT ud.1, u.name,  u.did
FROM test u
INNER JOIN (
  SELECT name,   COUNT(*)
  FROM test
  GROUP BY name
  HAVING COUNT(*) > 1
) t  ON  t.name= u.name
ORDER BY   u.name

Upvotes: 0

cmcau
cmcau

Reputation: 557

I would do this 

select id, name, did from test
where name in (select name from test group by name having count(*) > 1)

Upvotes: 0

Related Questions