mysql - Get all orders by "shipped" status that wasn't paid for by "wire transfer" -

i'm working directly magento db, trying list of orders order status of shipped, excluding paid via wire transfer (bankpayment). here's current query:

select * (   select      so.entity_id,      so.created_at,      max(soe.created_at) shipped_at    sales_order    left outer join sales_order_entity soe      on (soe.parent_id=so.entity_id)    left outer join sales_order_entity_varchar soev      on (soev.entity_id = soe.entity_id)      , (soev.value <> 'bankpayment')    (soev.value = 'shipped')    group so.entity_id) shipped  (shipped.shipped_at >= '2013-05-01 04:00:00')  , (shipped.shipped_at <= '2013-05-02 03:59:59'); 

i'm not sql expert, , can see i'm trying select orders not paid wire transfer line:

and (soev.value <> 'bankpayment')

but doesn't work expected, , suspect has where clause:

where (soev.value = 'shipped')

i don't know how write query. advice?

note - in case, i'm not able use magento models/collections gathering data.

you looking entities have 'shipped' not 'bankpayment'. want these values on different rows. in other words, want accept entity has row 'shipped' when there no rows 'bankpayment'.

you can logic in having clause. well, don't quite have having clause. have subquery where clause on outside. although equivalent, i'm simplifying query eliminate subquery. then, i'm using having clause check dates , presence/absence of 2 values:

select so.entity_id, so.created_at,         max(soe.created_at) shipped_at  sales_order left outer join      sales_order_entity soe       on soe.parent_id = so.entity_id left outer join      sales_order_entity_varchar soev       on soev.entity_id = soe.entity_id group so.entity_id having max(soe.created_at) >= '2013-05-01 04:00:00' ,         max(soe.created_at) <= '2013-05-02 03:59:59' ,        sum(soev.value = 'shipped') > 0 ,        sum(soev.value = 'bankpayment') = 0;