php - MySQL: Ignore row when summing a field if another table contains a certain value -

i have 3 tables, 1 called orders (containing customer info), called orders_total (containing order subtotals, discounts, , totals), , last 1 called orders_products (containing names , prices of items being ordered). tied common field called orders_id.

i trying aggregate (sum) total sales revenue except orders contain item x (from orders_products table), reason sum of sales revenues aren't adding when introduce third table (orders_products). know how aggregation , item exception separately 2 of 3 tables, complexity me combining both functions introducing third table. here working query far 2 original tables:

select o.orders_id, ot.orders_id, o.delivery_state, sum(ot.revenue) ordersum orders_total ot, orders o ot.orders_id = o.orders_id group o.delivery_state order ordersum desc; 

how implement exception ignore orders contain item x?

you can exclude orders original query identified via subselect:

select o.orders_id, ot.orders_id, o.delivery_state, sum(ot.revenue) ordersum orders_total ot inner join orders o on ot.orders_id = o.orders_id ot.orders_id not in (select orders_id orders_products product_name = '?') group o.delivery_state order ordersum desc; 

note ? represents product_name trying exclude.

you don't want orders_id information part of select, meaningless data regards aggregation.

for query run make sure have indexes on:

orders_products.product_name orders.delivery_date orders.orders_id orders_total.orders_id