query to select columns from a row in which another column has certain value only SQL-CodePudding

Consider the following table

id   attribute
1     a
1     a
1     b
2     a
2     a
3     c
4     a

I want to select the ids that have attribute of 'a' only, ie 2 and 4. Cant select 1 because 1 has 'a' and 'b', cant select 3 because it has 'c' only. We select 2 and 4 because it has 'a' value only.

CodePudding user response：

Please test this:

SELECT id
FROM attribute
GROUP BY id
HAVING 
COUNT(DISTINCT attribute) = 1 AND MIN(attribute)= 'a';

CodePudding user response：

You can use

SELECT id
FROM   YourTable
GROUP  BY id
HAVING MAX(attribute) = 'a' AND MIN(attribute) = 'a'
       AND COUNT(*) = COUNT(attribute)

the

COUNT(*) = COUNT(attribute)

is to discard any id that have NULL attribute as well as a. Remove this if that is not the semantics you want or the column is not nullable anyway.