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'))>