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?