SQL query too complicated for me

Question

I have a table with a note column and there can be value 'Start' or 'End'. And then there are other columns, that can have same value, but only difference is in that 'note' column...

I need to select rows which have the 'note' set to 'Start', but only those, there are no row with the same values and 'note' set to 'End'. Sorry, it's complicated to explain. I'll try to show some example.

Coll1   Coll2   Coll3   note
-----------------------------
a       a       a       Start
a       a       a       End
b       b       b       Start
b       b       b       End
c       c       c       Start <- I need select those rows
-- There is no row with 'c c c End' combination in the table
d       d       d       Start
d       d       d       End
e       e       e       Start <- I need select those rows
-- There is no row with 'e e e End' combination in the table

Can anybody help me please?

Answer 1

Try to use

SELECT *
  FROM tbl t1
  WHERE t1.note = 'Start' AND NOT EXISTS (SELECT * 
                                            FROM tbl t2 
                                           WHERE t2.note = 'End' 
                                             AND t2.Coll1 = t1.Coll1 
                                             AND t2.Coll2 = t1.Coll1 
                                             AND t2.Coll3 = t1.Coll3)

Maybe this query not optimal, but this query is easy for understand.

Answer 2

The most simple way should be to aggregate the records and check whether there is an End record for the group:

select col1, col2, col3
from mytable
group by col1, col2, col3
having count(case when note = 'Start' then 1 end) = 1
   and count(case when note = 'End' then 1 end) = 0;

Adjust this as you like (e.g. if you are fine with several start records make it >= 1 instead of = 1).