Find instance that has specified attribute-CodePudding

name	attribute
item1	data1
item2	data1
item3	data2
item1	data2
item2	data3
item4	data3
item3	data2
item5	data3

I am trying to find names that only have attribute data3. Expected output :

name
item4
item5

My solution was to SELECT name FROM TABLE then EXCEPT SELECT name WHERE attribute!="data3" but this included more items than expected output. I am trying to solve the problem with set operations such as EXCEPT, UNION, etc.

CodePudding user response：

Using NOT IN :

SELECT DISTINCT
    name
FROM
    table
WHERE
    attribute = 'data3'
    AND
    name NOT IN (SELECT name FROM table WHERE attribute != 'data3')
;

Or using EXCEPT:

SELECT name FROM table WHERE attribute  = 'data3'
EXCEPT
SELECT name FROM table WHERE attribute != 'data3'
;

CodePudding user response：

select   name
from     t
group by name
having   max(attribute) = 'data3'
and      max(attribute) = min(attribute)

name
item4
item5

Fiddle