how to find people with same family name?

You have a table with 4 columns:

Primary key / name / surname / 开发者_JS百科middle name

How to write Sql query to find people who has same family name?

1 / Ivan / Ivanov / Ivanovich

2 / Petr / Levinsky / Aleksandrovich

3 / Alex / Ivanov / albertovich

Should return Ivan and Alex

Thanks

In standard SQL you can simply join the table with itself:

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 distinct pairs of first names for every surname that has multiple entries.

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

If you want to find exactly names then you should firstly find all surnames that appear more than once and the find all names:

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

As for me easiest way is to group records by surname and then select those with count more than 1.

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

Untested:

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

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