How to find people with the same name?

You have a table with 4 columns:

Primary Key / First Name / Last Name / Middle Name

How to write an Sql query to search for people having the same name?

1 / Ivan / Ivanov / Ivanovich

2 / Peter / Levinsky / Aleksandrovich

3 / Alex / Ivanov / albertovich

Must return Ivan and Alex

thanks

+6
source share
5 answers

In standard SQL, you can simply attach the table to yourself:

select a.name, b.name from t as a, t as b where a.surname = b.surname and a.id < b.id 

where t is your table and id is the primary key column.

This returns all even pairs of first names for each last name with multiple entries.

You might want to add surname to the list of selected columns.

+9
source

If you want to find the exact names, you must first find all the last names that appear more than once and find all the names:

 select name from t where surname in (select surname from t group by surname having count(surname) > 1); 
+8
source

You want GROUP BY surname, and then use the HAVING clause to find groups that have> 1.

Unverified:

 SELECT name FROM theTable WHERE Surname IN ( SELECT Surname FROM theTable GROUP BY Surname HAVING COUNT(Surname) > 1) 
0
source

For me, the easiest way is to group entries by last name, and then select those with a number greater than 1.

0
source
 select surname,group_concat(firstname) from people group by surname having count(firstname)> 1; 
-1
source

Source: https://habr.com/ru/post/886946/


All Articles