>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?
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 NOT EXISTS (SELECT * FROM items items2;
WHERE cms.order_no=items2.order_no
AND items2.item_state not in ('PI', 'CM'))