4

I want to know how can I find duplicate value in a table over two columns combined.

suppose my table has fields as id || name || father_name || region || dob

now how can I find results set such as:

enter image description here

.ie I want to find all rows where three columns are same.

Dean Ambrose
  • 183
  • 10
  • Possible duplicate of [MySQL select records for duplicates using multiple columns](https://stackoverflow.com/questions/16324328/mysql-select-records-for-duplicates-using-multiple-columns), [Find duplicate records in MySQL](https://stackoverflow.com/questions/854128) – Marc L. May 29 '18 at 12:00

3 Answers3

5
select t1.*
from your_table t1
join
(
    select name, father_name, region
    from your_table
    group by name, father_name, region
    having count(*) >= 3
) t2 on t1.name = t2.name 
    and t1.father_name = t2.father_name
    and t1.region = t2.region
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • when I remove the id from group by it gives me `#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.candidate_list.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by` – Dean Ambrose May 29 '18 at 11:21
  • actually i want duplicates in name father_name and city only I wanted the id of the record to be printed as well so as to identify them – Dean Ambrose May 29 '18 at 11:24
  • I updated the answer. The inner join gets the revevant values and the outer select gets the complete records. – juergen d May 29 '18 at 11:26
  • I was wrong about previous comment BTW `count(*) > 1` should be `count(*) > 2` to retrieve where three columns are same – Shushil Bohara May 29 '18 at 11:43
  • you mean 3 records and not 3 columns – juergen d May 29 '18 at 11:51
1

If you are using MySql 8.0, you could make use of window function. Below query with such function returns exact output:

select id, name, fatherName, country from (
    select id,
           name,
           fatherName,
           country,
           count(id) over (partition by name, fatherName, country) cnt
    from Tbl
) `a` where cnt > 1;
Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
0

Actually, i also need this type of feature many times, where i need to compare all columns with same value except auto incremented primary key id column.

So, in that case i always use group by keyword.

Example,

SELECT A.*
FROM YourTable A
INNER JOIN (SELECT name,city,state
        FROM YourTable
        GROUP BY name,city,state
        HAVING COUNT(*) > 1) B
ON A.name = B.name AND A.city = B.city AND A.state = B.state

You can append the number of columns which you want to compare

Hope, This might help you in your case also.

Hetal Chauhan
  • 787
  • 10
  • 22
  • How is this different from already given answer by @juergend ?? – Michał Turczyn May 29 '18 at 12:35
  • i have not seen his answer, i just seen the question and posted the sql query which i always use. – Hetal Chauhan May 29 '18 at 12:36
  • Ok, but this looks like copy & paste. While you might not seen it, your answer doesn't bring anything new to the question. – Michał Turczyn May 29 '18 at 12:39
  • Also, now i seen his answer and i noted that both sql query are different. It is not necessary that if already one answer with correct one has already been posted then no one can post their answer. I have just shared my answer. – Hetal Chauhan May 29 '18 at 12:40
  • How are they different, except column names, which in your case don't even match OPs columns? – Michał Turczyn May 29 '18 at 12:40
  • oops, actually their is little minor change, he has used default keyword JOIN, whereas i have put INNER JOIN. Well, i will take care of it next time. – Hetal Chauhan May 29 '18 at 12:45