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;