Select distinct columns where another column does not contain a particular value-CodePudding

I have a table that looks like this

id	name	col1
1	foo	A
2	foo	C
3	bar	A
4	bar	D

I want to select all names where col1 has no entry D

The result should be "foo"

I tried

SELECT DISTINCT name FROM table WHERE col1 != D

But it returns "foo" and "bar" because of id 3

CodePudding user response：

Avoid the over use of DISTINCT.

SELECT name
FROM table
GROUP BY name
HAVING SUM(col1='D') = 0

As an aggregate over names is needed GROUP BY name. HAVING applies after the aggregation. col1='D' is a 1/0 expression so summing them up and you want 0 to be the total.