Try

SELECT Cms.order_no, Cms.order_st2, Items.item_id, Items.item_state;
	FROM ;
    	 cms Cms ;
	JOIN items Items ;
		ON  Cms.order_no = Items.order_no;
	WHERE  Cms.order_st2 = ( 'PI' ) ;
		AND EXISTS ( SELECT * FROM Items i2 ;
				WHERE I2.order_no = Items.order_no AND item_state = "PI") ;
		AND EXISTS ( SELECT * FROM Items i2 ;
				WHERE I2.order_no = Items.order_no AND item_state = "CM") 

>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?