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?