Hi David,
I don't see why do you need lots table in this select. Try
SELECT st1.date, st1.name, ;
st1.status_id, st1.stats_id, st1.lots_id ;
FROM status st1 ;
WHERE st1.stats_id = event1val ;
AND NOT EXISTS (SELECT * FROM status st2 ;
WHERE st1.lots_id = st2.lots_id ;
AND st2.stats_id = event2val)
>Can the following be done in a single Select command?
>
>The following will return all shipments (lots) that have encountered a specific event (ie. Picked Up). How can I specify to show all shipments that match event1 but not event2 (ie. Picked Up, but not Delivered)?
>
>***********
> event1val = 19432 && id of first eventlevel
> event2val = 19412 && id of second event level
>
> select status.date, status.name, ;
> status.status_id, status.stats_id, lots.lots_id ;
> from lots, status ;
> where status.stats_id == event1val ;
> and status.lots_id = lots.lots_id
> GROUP BY lots_id
> into CURSOR showme
>************
>
>
>Thanks very much for any guidance.
>
>David
--sb--