1

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.

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.

0
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
  • 1
    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. – hot2use Sep 17 '18 at 8:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.