dufffman@gmail.com wrote:
> Hi,
>
> We have a bunch of events that take place everyday. At the end of
> each day we want to find out all the events that have not been
> completed.
>
> This is the structure of the table (eventId, eventName, status)
>
>
> 1 Event1 P
> 1 Event1 R
> 1 Event1 C
> 2 Event2 P
> 2 Event2 R
> 2 Event2 C
> 3 Event3 P
> 3 Event3 R
> 3 Event3 C
> ...
> ..
> ..
>
>
> I am trying to write a query that will return me all events that dont
> status R or C. I know how to do it with cursor logic, and using
> various temp tables, but I am sure there is something in set logic
> that could be applied here.

select *
from eventTable e1
where not exist (select * from eventTable 2 where e1.eventId = e2.eventId
and status = 'R')
and not exist (select * from eventTable 2 where e1.eventId = e2.eventId and
status = 'C')

> select id
> from eventTable e
> where e.date = '20060206'
> group by eventId
> having count(*) < 3
>
> The above would give me all entries that have less than 3 events
> associated w/them. But I also watned to check that the three status'
> that it has are P, R, and C. thats where I am stuck.. any ideas?

But how? Since count(*) < 3 you can only have two status'.

But it sounds to me like you will need some kind of self-join, maybe along
these lines:

select
from eventTable e1
join eventTable e2
on e1.eventId = e2.eventId
join eventTable e3
on e3.eventId = e2.eventId
where e1. status = 'P'
and e2.status = 'R'
and e2.status ='C'
and e1.date = '20060206'


--
Kristian Damm Jensen