Finding if a value does not exist in SQL

My data is structured with 5 columns (let’s call them a, b, c, d, e) and 1,000,000+ rows. Each value in b has the potential for ~50 possibilities in e – so there could be up to 50 lines for each unique b value. Every b should have a '-27' among their e values. I would like to query all UNIQUE b where it doesn’t have the -27 e value, ignoring all other possibilities for e.

Code so far:

select a, b, c, d, e
from TestDB
where not exists (select count(distinct b) from TestDB where e = '-27')

Would this code be sufficient? In initial tests I’ve done it appears to be either a) working or b) returning nothing. I’m new to SQL so I appreciate any help or being pointed in the right direction!

**edited to make it clearer I was looking for unique 'b' values.

Answer

I would like to query all b where it doesn’t have the -27 e value, ignoring all other possibilities for e.

If you just want the b values, then aggregation should work:

select b
from testdb t
group by b
having sum(case when e = '-27' then 1 else 0 end) = 0;

Advertisement

Answer