[OK, just re-read the other posts ... cancelled my first post ...]

Some assumptions:

1 - an eventId will have 1, 2 or 3 records in the eventTable

1a - if there is only one record then status will always be 'P'

1b - if there are 2 records then status will always be 'P' and 'R'

1c - if there are 3 records then status will always be 'P', 'R' and 'C'

[In other words, you always start with a 'P', then add an 'R', then add a
'C'; you can't have an 'R' unless you have a 'P'; you can't have a 'C'
unless you have an 'R']

2 - you only want the eventId's that have one or two records in eventTable
(ie, they only have a status of 'P' or 'R' with no associated records
having a status of 'C')

-----------------

First idea:

select eventId, count(*)
from eventTable
group by eventId
having count(*) = 2

If you don't like/want the 'count(*)' in the output list you can try:

select eventId
from eventTable
group by eventId
having count(*) = 2

The problem with this query is that you're depending on Sybase's
un-Ansi-like behaviour to return the values you want.

Another alternative, if you're running ASE 12.5.1+, is to use a derived table:

select eventId
from (select eventId, count(*)
from eventTable
group by eventId
having count(*) = 2) derived_table

------------------------------

And if none of this gives you what you're looking for, please post back
with a set of sample raw data and what you would expect as a result set
from your proposed query.

(Make sure you include some raw data that will show up in the result set,
and some raw data that won't show up in the result set.)





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 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?
>
> Help is much aprpeciated.
>
> Cheers,
>