How to select records with two columns matching values in one table in PostgreSQL?

Question

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.

• user1822: order by father_name, name?
• Abdul Raheem Ghani: @a_horse_with_no_name : Yes, they should come together.

Answer 1

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.

Answer 2

select id, name, father_name
 from employee
 where (name, father_name) in (
 select name, father_name
 from employee
 group by name, father_name
 having count(*) > 1
 )
 order by father_name, name

• John K. N.: Please consider reading the following article: How do I write a good answer? (Help Centre) In the section Answer the question there is a short sentence which reads: Brevity is acceptable, but fuller explanations are better. You might want to consider editing your answer and adding an explanation of why your answer is better. Your answer might be flagged as very low quality.