Ad

SQL To Fetch Value Of One Column Such That A Certain Value In Another Column Does Not Exist

- 1 answer

I have the following Table t

keyvalue
1a
1b
1c
2a
2c

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

Ad

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
Ad
source: stackoverflow.com
Ad