Select table entries belonging to combinations of entries in another table (mysql) -

regarding mysql queries, how can table entries belong combinations of entries in table?

background: count orders consist of order items. each order item has state 'canceled' or 'delivered'. there partial deliveries, there can both delivered , canceled orders in 1 order. count net orders , know how many orders have items more 1 item status.

order number |  order item  |   status  x0001        |  item        |   delivered x0001        |  item        |   delivered x0002        |  item        |   delivered x0002        |  item        |   canceled x0002        |  item        |   delivered x0003        |  item        |   delivered 

i have 3 valid orders here , 1 order delivered/canceled. how can ask orders have @ least 1 delivered , 1 canceled item?

as new mysql looking right approach. need subqueries or joints this?

edit: first of all, sorry late reply. , sorry again because question misleading.

there 3 tables involved: 'order', 'order_item' , 'order_item_status'.

'order' , 'order_item' linked through 'order_item.fk_order' , 'order.id_order'.

'order_item' , 'order_item_status' linked through 'order_item.fk_order_item_status' , 'order_item_status.id_order_item_status'.

you have been helpful far still bit stuck not know how count combination. perfect result that:

{shipped}            |  34 {canceled}           |  12 {shipped, canceled}  |  8 {closed, canceled}   |  4 {closed}             |  27 ...                  |  ... 

but don't know how deal combinations in query. maybe have more helpful hints me ...

thank much.

i assume have 2 table: orders (list of orders) order_items (list of itemd of order) 2 table relationed field fk_order.

try this:

select o.id, (select count(*) order_items i.status = 'delivered' , i.fk_order = o.id) item_delivered, (select count(distinct i.status) order_items i.fk_order = o.id) status_no orders o