SQL: query one column in same table

Question

A football manager here. How do I:

1. Select all matches that have kicked-off but never had a goal.

2. Select all matches that kicked-off more than 1h ago but haven't yet had a goal or a corner-kick.

   | Match | Event       | EventTime           |
   |-------------------------------------------|
   | 1     | Kick-off    | 2014-12-15T16:00:00 |
   | 1     | Throw-in    | 2014-12-15T16:15:00 |
   | 1     | Goal        | 2014-12-15T16:20:00 |
   | 1     | Corner-kick | 2014-12-15T16:30:00 |
   | 1     | End         | 2014-12-15T17:30:00 |
   | 2     | Kick-off    | 2014-12-10T16:00:00 |
   | 2     | Goal        | 2014-12-10T16:01:00 |
   | 3     | Kick-off    | 2014-12-05T08:00:00 |
   | 3     | Corner-kick | 2014-12-05T08:10:00 |
   

I feel this should be simple, but I'm stuck somehow.

Answer 1

1:

SELECT DISTINCT Match
FROM dbo.YourTable A
WHERE [Event] = 'Kick-off'
AND NOT EXISTS( SELECT 1 FROM dbo.YourTable 
                WHERE Match = A.Match
                AND [Event] = 'Goal')

2:

SELECT DISTINCT Match
FROM dbo.YourTable A
WHERE [Event] = 'Kick-off' 
AND EventTime <= GETDATE(HOUR,-1,GETDATE())
AND NOT EXISTS( SELECT 1 FROM dbo.YourTable 
                WHERE Match = A.Match
                AND [Event] IN ('Goal','Corner-kick'))

Answer 2

You would do this with aggregation and a having clause. For the first:

select match
from table t
group by match
having sum(case when event = 'Kick-off' then 1 else 0 end) > 0 and
       sum(case when event = 'Goal' then 1 else 0 end) = 0;

For the second:

select match
from table t
group by match
having max(sum case when event = 'Kick-off' then eventtime end) <= getdate() - 1.0/24 and
       sum(case when event in ('Goal', 'Corner-Kick') then 1 else 0 end) = 0;

Each condition in the having clause counts the number of rows that match the condition. > 0 means that at least one row matched. = 0 means no rows match.