9

i have a table like this

ID     nachname     vorname
1       john         doe
2       john         doe
3       jim          doe
4       Michael     Knight

I need a query that will return all the fields (select *) from the records that have the same nachname and vorname (in this case, records 1 and 2). Can anyone help me with this? Thanks

user347033
  • 93
  • 1
  • 1
  • 4

4 Answers4

17

The following query will give the list of duplicates :

SELECT n1.* FROM table n1
inner join table n2 on n2.vorname=n1.vorname and n2.nachname=n1.nachname
where n1.id <> n2.id

BTW The data you posted seems to be wrong "Doe" and "Knight" are a lastname, not a firstname :p.

wimvds
  • 12,790
  • 2
  • 41
  • 42
  • 2
    I just needed to add select distinct (the query was returning 2 times the same row). Thank you for your help – user347033 May 21 '10 at 14:48
  • Note: if fields "vorname" and "nachname" are nullable then "coalesce" function should be used for comparison. See: https://stackoverflow.com/questions/9608639/mysql-comparison-with-null-value/62260762#62260762 – domis86 Feb 25 '21 at 14:20
  • [@domis86](https://stackoverflow.com/questions/2881774/get-list-of-duplicate-rows-in-mysql#comment117336762_2882047): or you could use the NULL-safe equal to operator (`<=>`), which compares nullable columns. Reference: https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_equal-to – Lucio Mollinedo Jul 04 '22 at 21:01
13

The general solution to your problem is a query of the form

SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

This will return one row for each set of duplicate row in the table. The last column in this result is the number of duplicates for the particular values.

If you really want the ID, try something like this:

SELECT id FROM 
t1, 
( SELECT col1, col2, count(*)
  FROM t1
  GROUP BY col1, col2
  HAVING count(*) > 1 ) as t2
WHERE t1.col1 = t2.col1 AND t1.col2 = t2.col2

Haven't tested it though

ewernli
  • 38,045
  • 5
  • 92
  • 123
2

You can do it with a self-join:

select distinct t1.id from t as t1 inner join t as t2 
on t1.col1=t2.col1 and t1.col2=t2.col2 and t1.id<>t2.id

the t1.id<>t2.id is necessary to avoid ids matching against themselves. (If you want only 1 row out of each set of duplicates, you can use t1.id<t2.id).

David Gelhar
  • 27,873
  • 3
  • 67
  • 84
  • Nope, that one will only return 1 row with the 2 matching records in it, not the 2 rows that it should return... – wimvds May 21 '10 at 12:50
  • @wimvds true, if you want all duplicate rows (instead of 1 row of each duplicate set, you should use <>) – David Gelhar May 21 '10 at 12:55
0
select * from table AS t1 inner join
(select max(id) As id,nachname,vorname, count(*) 
from t1 group by nachname,vorname 
having count(*) >1) AS t2 on t1.id=t2.id

This should return ALL of the columns from the table where there is duplicate nachname and vorname. I recommend changing * to the exact columns that you need.

Edit: I added a max(id) so that the group by wouldn't be a problem. My query isn't as elegant as I would want though. There's probably an easier way to do it.

jle
  • 9,316
  • 5
  • 48
  • 67
  • Hmm... I see what you mean now. But I'm pretty sure your query is wrong. You can't return `id` if you're not using it to `group by`. – ewernli May 21 '10 at 12:15
  • That join doesn't work - there's no `id` column in the t2 query. – David Gelhar May 21 '10 at 12:19
  • This is just blatantly wrong... The group by will in fact eliminate any duplicates you have if you're using MySQL since you only group on nachname and vorname, so it will return 1 row, with 1 ID, instead of all distinctive rows as you probably expected (just try it, you'll see). Oh, and any other RDBMS would complain about your group by (which is imho the only correct way, I hate MySQL trying to guess what you want and execute these erronous queries instead of throwing an error). – wimvds May 21 '10 at 13:21