CODEWITHSUNDEEP
sqljoinsubquery
cal17_hogo
cal17_hogo

Reputation: 149

Return All Duplicate Rows With Matching Elements in Another Column

This is the original base table.

Original Table

I am looking to return all rows that have duplicate id values and have the same title for both of the duplicate id's.

So I am looking to return the rows 

3 CEO
3 CEO
6 Janitor
6 Janitor

So far I have only been able to return the rows with duplicate id values using this code

select id, title 
from original_table
where id in
    (select id 
    from original_table
    group by id
    having count(id) > 1);

enter image description here

Any suggestions on how to get the desired result?

Upvotes: 0

Views: 168

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

Add an additional condition:

select id, title 
from original_table
where id in
    (select id 
     from original_table
     group by id
     having count(id) > 1 and count(distinct title) = 1
    );

Upvotes: 1

Related Questions