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.
Advertisement
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;