Llama Llama - 1 month ago 6
SQL Question

SQL query too complicated for me

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

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.