how to find people with same family name?

Question

You have a table with 4 columns:

Primary key / name / surname / 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

Answer 1

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.

Answer 2

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);