Find rows with duplicate values in two columns where at least one value in one column is a specific value

So, I’m not sure how it works and I haven’t found a sufficient answer by googleing (probably not using the right buzz words). So here it comes: Let’s say I have a table like this, let’s call it persons

ID	Name	First Name	Country
1	Doe	John	USA
2	Doe	John	UK
3	Doe	John	Brazil
4	Meyer	Julia	Germany
5	Meyer	Julia	Austria
6	Picard	Jean-Luc	France
7	Picard	Jean-Luc	UK
8	Nakamura	Hikaro	Japan

Ok, so now I want to select all the rows that have the same name and first name and where at least one country is the UK. So my result set should look like this.

ID	Name	First_Name	Country
1	Doe	John	USA
2	Doe	John	UK
3	Doe	John	Brazil
6	Picard	Jean-Luc	France
7	Picard	Jean-Luc	UK

I mean, I know how to find doubles in general like this

SELECT *
FROM persons  p1
JOIN (SELECT NAME, FIRST_NAME, count(*) FROM PERSONS  
GROUP BY FIRST_NAME, NAME having count(*) >1) p2
ON p1.NAME = p2.NAME 
AND p1.FIRST_NAME = p2.FIRST_NAME;

But this also results in having Julia Meyer in there and I don’t want her in there.

Any suggestions?

Answer

Use EXISTS:

SELECT p1.*
FROM persons p1
WHERE EXISTS (
  SELECT *
  FROM persons p2
  WHERE p2.ID <> p1.ID
    AND p2.Name = p1.Name AND p2.FirstName = p1.FirstName 
    AND 'UK' IN (p1.Country, p2.Country)
);

See the demo.

Advertisement

Answer