dufffman@gmail.com wrote:
> Kristian Damm Jensen wrote:
>> 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'.
>>
>
> Precisely.. I want all entries that have 1 or 2 status' in it. Thus
> all entries that are not in the completed state yet.
>
>
>> But it sounds to me like you will need some kind of self-join, maybe
>> along these lines:
>>
>
> I think this is something like what I was looking for. Which leads me
> to my next quesetion. This would give me all the events that are good
> (meaning they had entered the pending state, running state and are now
> in the complete state).
>
> So What i really need is something like..
>
> All events
> MINUS
> 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'

i.e. all events that are not good?

If so you are on the right track and were so from the start.

Try

select *
from eventTable e1
where e2.date = '20060206'
and not exists
(select eventID from eventTable e2
where e1.eventId = e2.eventID
group by eventID
having count(*) = 3)

The subselect will find all those that are good (my select above will too,
but theres no reason to do a triple selfjoin for that),

On the other hand, if I understand correctly the records for an event will
include the statuses (?) 'P', 'R' and 'C' and in that order, and you are
interested in those events without status 'C'. If so, it can be made even
more simple:

select *
from eventTable e1
where e1.date = '20060206'
and not exists
(select eventID from eventTable e2
where e1.eventId = e2.eventID
and e2.status = 'C')


--
Kristian Damm Jensen