CODEWITHSUNDEEP
mysql
Idro
Idro

Reputation: 349

How to find duplicates with a query in MySQL database

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.

Upvotes: 1

Views: 81

Answers (4)

Pavel Komarov
Pavel Komarov

Reputation: 11

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)

Upvotes: 1

juergen d
juergen d

Reputation: 204766

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

Upvotes: 1

Gerald Chablowski
Gerald Chablowski

Reputation: 472

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

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

Upvotes: -1

sgeddes
sgeddes

Reputation: 62841

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
    )

Upvotes: 2

Related Questions