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'.]
2 - you only want the eventId's that have one record in eventTable (ie,
they only have a status of 'P' with no associated records having a status
of 'R' or 'C')
-----------------
First idea:
select eventId, count(*)
from eventTable
group by eventId
having count(*) = 1
If you don't like/want the 'count(*)' in the output list you can try:
select eventId
from eventTable
group by eventId
having count(*) = 1
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(*) = 1) derived_table
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,
>