CODEWITHSUNDEEP
sql
VextoR
VextoR

Reputation: 5165

how to find people with same family name?

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

Upvotes: 7

Views: 23307

Answers (5)

Fermin
Fermin

Reputation: 36091

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)

Upvotes: 0

NPE
NPE

Reputation: 500475

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.

Upvotes: 13

andr
andr

Reputation: 1646

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

Upvotes: 9

DixonD
DixonD

Reputation: 6628

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

Upvotes: 0

ADW
ADW

Reputation: 4080

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

Upvotes: -1

Related Questions