PostgreSQL Query – How to Select Records with Two Columns Matching Values

postgresqlquery

I have a table with the below structure and data: 

create table employee (id int, name varchar, father_name varchar);
insert into employee values(1, 'John', 'Alex'),(2, 'Simi', 'Expatri'),(3, 
'John', 'Alex'),(4, 'Hezad', 'Ambrose'),(5, 'John', 'Alex'), (6, 'Simi', 
'Expatri'), (7, 'Hezad', 'Ambrose'), (8, 'John', 'Reman'), (9, 'Komal', 
'Ambrose');

Now I want to fetch those records whose two columns name and father_name match each other.
The desired result would be as following: 

id    |    name    |    father_name
1     |    John    |    Alex  
3     |    John    |    Alex  
5     |    John    |    Alex  
2     |    Simi    |    Expatri  
6     |    Simi    |    Expatri  
4     |    Hezad   |    Ambrose  
7     |    Hezad   |    Ambrose

Any help is appreciated in advance.

Best Answer

Ordering by name and father_name is the first step, but I assume you don't want records where no other matching records are found. This would work:

select e1.id, e1.name, e1.father_name
  from employee as e1
  inner join employee as e2
    on e1.name = e2.name
   and e1.father_name = e2.father_name
   and e1.id != e2.id
  group by e1.id, e1.name, e1.father_name
  order by e1.name, e1.father_name

Here is a working demo.

Related Question