I have the following Table t

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

CodePudding user response：

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

CodePudding user response：

Try this

with b_keys as
(
    select distinct key
    from t
    where value = 'b'
)

select distinct key
from t
left join b_keys
  on t.key = b_keys.key
where b_keys.key is null

CodePudding user response：

Are you really looking explicitely for "b"?

Or do you actually need to find whatever keys are missing - ie - it might not be "b" but "a" or "c" that was missing?

If you build a list of all expected values you can then use it with a not exists to identify all keys that are missing any values:

with v as (
    select distinct t.[key], dv.[value]
    from (select distinct [value] from t) dv
    cross join t
)
select [key]
from v
where not exists (select * from t where t.[value] = v.[value] and t.[key] = v.[key]);

See Demo