Good afternoon. The database has a field with a value, for example, "Sergey Ivanov". It is necessary that the record "Ivanov Sergey" was recognized as a double of the record "Sergey Ivanov". is it possible only to use MySql to make a selection that will result in the id of these two records

  • Do you need the id of all duplicates in the table or only one specific input? - Victor Zharikov
  • I need to find duplicates of all users - IK
  • one
    php in tags for what? It can not be used according to the conditions of the question. - Victor Zharikov
  • one
    Your task is not so simple: to recognize variants of the same name. See, "Ivanov Sergey" and "Sergey Petrovich Ivanov" is also the same name. "Jean d'Alembert" and "Jean d'Alembert" - too. There is also the difference between "Natalia" and "Natalia". You will have to consider all these options sooner or later. Are you still hoping to do this at the database level? - VladD
  • So deep is not necessary. Any such options will be considered as different people. Interests Name. Last Name - Last Name. If it is impossible to solve it using only mysql, tell me the solutions, please - IK

2 answers 2

SELECT t1.id AS id1, t1.name AS name1, t2.id AS id2, t2.name AS name2 FROM test t1, test t2 WHERE NOT t1.id=t2.id AND ( t1.name = t2.name OR ( SUBSTRING_INDEX( t1.name , ' ', 1 ) = SUBSTRING_INDEX( t2.name , ' ', -1 ) AND SUBSTRING_INDEX( t1.name , ' ', -1 ) = SUBSTRING_INDEX( t2.name , ' ', 1 ) ) ) GROUP BY CONCAT(LEAST(t1.id, t2.id),GREATEST(t2.id,t1.id))

Data

 "1","василий петров" "2","петров василий" "3","иванов сергей" "4","иванов сергей" "5","юля мальцева" "6","мальцева юля" "7","кирилл васечкин" "8","сергей мямликов"

Conclusion

 id1 name1 id2 name2 2 петров василий 1 василий петров 4 иванов сергей 3 иванов сергей 9 сергей иванов 3 иванов сергей 9 сергей иванов 4 иванов сергей 6 мальцева юля 5 юля мальцева
  • Thank you, but I ran incorrectly out of 17,000 entries as a result of 1,300,000 - IK
  • We didn’t forget to filter out NOT t1.id=t2.id ? Otherwise, it will naturally glue everyone with everyone. Show an example of an incorrect definition. And it is better to lay out the question of the table structure. - rjhdby
  • @IK I had in mind an example of an incorrect sample of this query. Type "10, Vasily Petrov, 23, Gennady Alliluyev" - rjhdby
  • I have doubts about the correctness of the code that I compiled - SELECT t1.id, t1.name, t2.id, t2.name FROM users AS t1, users AS t2 .... - IK
  • 95692 Levanova Olga 60022 Levanova Olga 86269 Irina Gordienko 60025 Gordienko Irina - IK
 CREATE TABLE people (`id` int, `name` varchar(100)) ; INSERT INTO people (`id`, `name`) VALUES (1, 'Сергей Иванов'), (2, 'Иванов Сергей') ;

Further

  SELECT id FROM people WHERE name ='Сергей Иванов' or name = CONCAT( SUBSTRING_INDEX('Сергей Иванов',' ',-1), ' ', SUBSTRING_INDEX('Сергей Иванов',' ',1) );
  • And how to make for all records, and not just for these two? - IK
  • From where you call the request, substitute for Sergei Ivanov the right words. Or do you need to find duplicates in the entire database? - mxSandr
  • Search the entire database - IK
  • SELECT DISTINCT t1.id FROM people as t 1 INNER JOIN - mxSandr