FYI -
SET DELETED ON was my problem...arrrggh!
>Well, this seems to be a good one...but..
>If I put this in a remote view, I acutally get a different set of results than if I dont use a view...????
>I have SET ENGINEBEHAVIOR 70
>
>
>
>
>>How about something like????
>>SELECT Cms.order_no, Cms.order_st2, Items.item_id, Items.item_state;
>> FROM ;
>> cms Cms ;
>> LEFT OUTER JOIN items Items ;
>> ON Cms.order_no = Items.order_no;
>> WHERE Cms.order_st2 = ( 'PI' ) AND ;
>> (items.item_state in ('PI', 'CM'));
>> AND cms.order_no IN (SELECT cms2.order_no FROM cms cms2,items items2;
>> WHERE cms2.order_no=items2.order_no
>> AND cms2.order_st2="PI"
>> AND items2.item_state in ('PI', 'CM');
>> GROUP BY 1 HAVING COUNT(DISTINCT items2.item_state) = 2)
>>
>>
>>
>>
>>>SELECT Cms.order_no, Cms.order_st2, Items.item_id, Items.item_state;
>>> FROM ;
>>> cms Cms ;
>>> LEFT OUTER JOIN items Items ;
>>> ON Cms.order_no = Items.order_no;
>>> WHERE Cms.order_st2 = ( 'PI' ) AND ;
>>> (items.item_state in ('PI', 'CM'))
>>>
>>>
>>>...what my problem is that I want this to show ONLY the records where there is both 'PI' and 'CM' in items.item_state field for a given Order_no. Is it possible to do this in a select statement or am I stuck writing a tiny prg to do it?
ICQ 10556 (ya), 254117