Leave all entries out if there is a duplicate-CodePudding

Given the (somewhat silly) dataset of:

Name      Colour

Mark      Red
Mark      Yellow
Mark      Red

Sarah     Blue
Sarah     White

I would like to write an SQL query, that would return:

Sarah     Blue
Sarah     White

Having disregarded all "Mark" entries entirely because of the duplicate colour. How would I facilitate this?

CodePudding user response：

Firstly, I've tried to look out for names having duplicate colour and then ignored such names from our dataset. Assuming your table name is table1

with temp as
    (
    Select Name,colour, count(*) from table1 group by Name,colour having count(*)>1
    )
    Select * from table1 where name not in (select distinct name from temp);

Method 2:

Select * from table1 where name not in (select distinct name from (Select Name,colour, count(*) from table1 group by Name,colour having count(*)>1));