Choose where a and next - c, skipping b

Question

I am wondering if it is possible to return a single row in SQL to show, using the table below as an example, only a row for id 2:

table1 ( id 2 and 4 are missing value b) id value 1 a 1 b 1 c 1 d 2 a 2 c 2 d 3 a 3 b 3 c 3 d 4 a 4 c 4 d 

Basically I want to find all instances where 'b' does not exist, but 'a' still exists for any id and returns a single row for any given id. I tried something like this, but it does not work as I would like:

 select * from table1 where not exists (select distinct value from table1 where value b) 

I would like the end result to be something that defines the values ​​where "b" does not exist, but "a" does (not displaying the value, not required for the final goal):

 result table id 2 4 

Answer 1

This should complete the task:

 select distinct id from table1 t where not exists ( select 1 from table1 tt where t.id = tt.id and tt.vallue = 'b' ) and exists ( select 1 from table1 tt where t.id = tt.id and tt.vallue = 'a' ) 

Below you have a shorter form. It may perform a better and clearer keyword, may be unnecessary if the (id, value) pair is unique.

 select distinct id from table1 t left join table1 tt on t.id = tt.id and tt.value = 'b' where t.value = 'a' and tt.id is null 

Answer 2

 SELECT id FROM table1 t1 WHERE value = 'a' AND NOT EXISTS ( SELECT * FROM table1 sub WHERE sub.id = t1.id AND sub.value = 'b' ) 

Answer 3

Not tested, but I think something like this will work.

 SELECT id FROM table1 WHERE value='a' AND id NOT IN(SELECT id FROM table1 WHERE value='b') GROUP BY id; 

Answer 4

EDIT : apologizes to Dooh. I just noticed that this answer is essentially a duplicate of the second Dooh request. I will leave it as an executable example.

This can be useful for comparing execution plans for different queries.

 declare @table1 as table ( id int, value varchar(10) ) insert into @table1 ( id, value ) values ( 1, 'a' ), ( 1, 'b' ), ( 1, 'c' ), ( 1, 'd' ), ( 2, 'a' ), ( 2, 'c' ), ( 2, 'd' ), ( 3, 'a' ), ( 3, 'b' ), ( 3, 'c' ), ( 3, 'd' ), ( 4, 'a' ), ( 4, 'c' ), ( 4, 'd' ), ( 5, 'a' ), ( 5, 'a' ), ( 5, 'b' ), -- Duplicate 'a's. ( 6, 'a' ), ( 6, 'a' ) -- Duplicate 'a's. select distinct L.id from @table1 as L left outer join @table1 as R on R.id = L.id and R.value = 'b' where R.id is NULL and L.value = 'a'