snippetsqlMinor
How to select records with two columns matching values in one table in PostgreSQL?
Viewed 0 times
postgresqlcolumnswithtablerecordstwoonehowvaluesselect
Problem
I have a table with the below structure and data:
Now I want to fetch those records whose two columns
The desired result would be as following:
Any help is appreciated in advance.
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 | AmbroseAny help is appreciated in advance.
Solution
Ordering by
Here is a working demo.
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_nameHere 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_nameContext
StackExchange Database Administrators Q#217758, answer score: 3
Revisions (0)
No revisions yet.