HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqlcolumnswithtablerecordstwoonehowvaluesselect

Problem

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.

Solution

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.

Code Snippets

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

Context

StackExchange Database Administrators Q#217758, answer score: 3

Revisions (0)

No revisions yet.