Querying for Unique Values When The Value Exists on Multiple Rows-CodePudding

Thank you for reading!

I want to find any value ("Name"), but I don't want that value if it exists on the same row as a "Type" that equals "Z".

As an example, in the table below, I want all Sam and Joe records, but I do not want any Bob records, because one of the rows that Bob exists on, contains a "Z" value in the "Type" field. If any Bob has a "Z", I want none of the "Bobs"

Name	Type
Bob	A
Bob	B
Bob	Z
Sam	A
Joe	A
Joe	B
Joe	C

You may have guessed my problem by now. If I attempt to select the records based on the "Type" field NOT containing "Z", then I will still get back the two "Bob" rows that contain "A" and the "B".

If any of the bob rows contain "Z", I don't want any "Bob" rows to return. See desired results below:

Name
Sam
Joe

Does anyone know how to achieve this in a SQL select statement?

Many thanks,

Logan

CodePudding user response：

You could group the query by the name and have a having condition that counts the types with the value "Z":

SELECT   name
FROM     mytable
GROUP BY name
HAVING   COUNT(CASE name WHEN 'Z' THEN 1 END) = 0

CodePudding user response：

SELECT t.name
FROM MyTable t
WHERE
NOT EXISTS (
               SELECT 1
               FROM MyTable
               WHERE
               name = t.name
               AND type = 'Z'
)