SQL to fetch value of one column such that a certain value in another column does not exist

I have the following Table t

key	value
1	a
1	b
1	c
2	a
2	c

In the above table, the Key 1 has three values i.e. a, b and c. However Key 2 has only two values i.e. a and c.

How to write a SQL that would return the Key that does NOT have the value ‘b’? In the above example the SQL must return the Key 2

Answer

You could do it like this by creating a counter for b values per key and then selecting only the keys with 0 b values:

SELECT 
   key
FROM (SELECT
         t.key, 
         SUM(CASE WHEN t.value = 'b' THEN 1 ELSE 0 END) AS number_of_b_values
      FROM t
      GROUP BY t.key)
WHERE number_of_b_values = 0