How to find duplicates with a query in MySQL database

Question

I need to find inside my table on my MySQL database duplicated values with the same name but with different sport. Here is an example of data:

John Smith    Athletics
Edward Green  Athletics
Edward Green  Fencing
Jim Brown     Rugby
Jim Brown     Rowing
Jim Brown     Sailing
Stan Smith    Football
Stan Smith    Football

Well, I'd like to make a query which give me this result:

Edward Green  Athletics
Edward Green  Fencing
Jim Brown     Rugby
Jim Brown     Rowing
Jim Brown     Sailing

As I said, only that values with the same name but different sport, in order to find namesakes.

sgeddes · Accepted Answer · 2016-08-18 17:36:06Z

2

Here's one option using exists:

select *
from yourtable t
where exists (
    select 1
    from yourtable t2
    where t.name = t2.name and t.sport != t2.sport
    )

SQL Fiddle Demo

answered Aug 18, 2016 at 17:36

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

juergen d · Accepted Answer · 2016-08-19 00:42:04Z

1

The inner select gets all names having more than one distinct sport. To also get the sports for those names, you have to join against the same table

select t1.*
from your_table t1
join
(
    select name
    from your_table
    group by name
    having count(distinct sport) > 1
) t2 on t1.name = t2.name

edited Aug 19, 2016 at 0:42

answered Aug 18, 2016 at 17:40

juergen d

205k40 gold badges303 silver badges377 bronze badges

1 Comment

Michał Perłakowski Over a year ago

Could you add some explanation?

Pavel Komarov · Accepted Answer · 2021-12-19 20:41:08Z

1

SELECT name
FROM (SELECT DISTINCT name, sport FROM My_table) AS Temporary_table_name
GROUP BY name
HAVING ( COUNT(name) > 1 )

With command

SELECT DISTINCT name, sport

you exclude duplicated combinations of name and sport (namely "Stan Smith - Football") and after it you can look for repeated names with peace of mind (because you know they won't refer to the same sport)

answered Dec 19, 2021 at 20:41

Pavel Komarov

111 bronze badge

Comments

Gerald Chablowski · Accepted Answer · 2016-08-18 17:40:51Z

-1

Another posbility with no suquery will be to use Having with a COUNT :

SELECT * 
FROM yourtable t
GROUP BY name
HAVING ( COUNT(name) > 1 )

answered Aug 18, 2016 at 17:40

Gerald Chablowski

4722 silver badges7 bronze badges

Collectives™ on Stack Overflow

How to find duplicates with a query in MySQL database

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related