join - Mysql Query that requires subtraction of fields that cannot be referenced -

our db stores customer orders in 2 tables: customerorders , customerorderlines. amongst other fields, there customerorders.type field determines (being = 1 or = 2) if customerorder invoice or creditnote.

we have report lists amount of units sold, profits etc current query, displays total units sold on period, ie, doesnt subtract number of credits, if any. here sql , example of results generates:

(apologies massive query follows :))

select l.name locationname , sr.name salesrepname , ct.name customertypename , c.name customername , c.id customer_id , c.code , s.name suppliername , p.description productname , p.id product_id , p.unitofmeasure , sum(col.vatableprice) totalsales , sum(col.vatprice) vat , sum(col.quantity) totalitems , sum(col.quantity * col.costprice) totalsalecost , sum(col.vatableprice) - sum(col.quantity * col.costprice) totalprofit ,(      select sum(col2.vatableprice) totalsales       customerorders co2       left join customerorderlines col2 on col2.customerorder_id = co2.id      left join customers c2 on c2.id = co2.customer_id      left join locations l2 on l2.id = c2.location_id      left join customertypes ct2 on ct2.id = c2.customertype_id      left join salesreps sr2 on sr2.id = c2.salesrep_id      left join products p2 on p2.id = col2.product_id      left join suppliers s2 on s2.id = p2.supplier_id      c.salesrep_id = c2.salesrep_id      , co2.type = 2 , p2.supplier_id = 179      , co2.orderdate >= '2010-01-01 00:00:00'      , co2.orderdate <= '2010-02-01 23:59:59' ) credits  ,(     select sum(col2.vatprice) totalvat customerorders co2     left join customerorderlines col2 on col2.customerorder_id = co2.id     left join customers c2 on c2.id = co2.customer_id     left join locations l2 on l2.id = c2.location_id     left join customertypes ct2 on ct2.id = c2.customertype_id     left join salesreps sr2 on sr2.id = c2.salesrep_id     left join products p2 on p2.id = col2.product_id     left join suppliers s2 on s2.id = p2.supplier_id     c.salesrep_id = c2.salesrep_id     , co2.type = 2     , p2.supplier_id = 179     , co2.orderdate >= '2010-01-01 00:00:00'     , co2.orderdate <= '2010-02-01 23:59:59' ) creditsvat  customerorders co left join customerorderlines col on col.customerorder_id = co.id left join customers c on c.id = co.customer_id left join locations l on l.id = c.location_id left join customertypes ct on ct.id = c.customertype_id left join salesreps sr on sr.id = c.salesrep_id left join products p on p.id = col.product_id left join suppliers s on s.id = p.supplier_id co.status_v = 5  , co.type = 1 , p.supplier_id = 179 , co.orderdate >= '2010-01-01 00:00:00' , co.orderdate <= '2010-02-01 23:59:59' group c.salesrep_id 

which, in case (grouping etc determined application code) generates 'per sales rep' report:

rep | totalitems | salesvalue| costofsales | profit   | vat      | credits | credits(vat) rep1| 937        | £5796.49  | £3606.49    | £2190.00 | £1013.73 | £220.12 | £38.57 rep2| 1905       | £11695.09 | £7314.95    | £4380.14 | £2045.32 | £268.85 | £47.00 rep3| 1074       | £6346.61  | £3950.53    | £2396.08 | £1109.76 | £54.89  | £9.57 rep4| 2687       | £16129.42 | £10171.65   | £5957.77 | £2820.46 | £839.15 | £146.78 

so, problem lies in totalitems absolute number of items sold (all customerorders of type = 1). credits field shows total cost of items in period of type = 2, ie returned. totalitems should have qty of credits deducted @ glance can seen has been sold , of course other fields need credits counterpart deducting each other reflect correct amounts items sold.

at first thought simple modification existing query, noticed not reference subquery aliases in select rewrote whole query using join (select ....) sales / join (select .....) credits reference sales.qty , credits.qty select @ top of query didnt scale @ unless doing little queries.

this how far got:

(yes, im querying different things here... simplest form of query: sales / credits single product)

select sr.name salesrepsname ,l.name locationname ,sup.name suppliername ,p.description productname ,sales.qty sold ,credits.qty credits ,sales.qty - credits.qty actualsold customerorders co left join customerorderlines col on col.customerorder_id = co.id left join customers c on c.id = co.customer_id left join products p on p.id = col.product_id left join salesreps sr on sr.id = c.salesrep_id left join locations l on l.id = c.location_id left join suppliers sup on sup.id = p.supplier_id  join (select sum(col.quantity) qty, sum(col.vatableprice) total customerorderlines col left join customerorders co on co.id = col.customerorder_id col.product_id = 27642 , co.type = 1) sales join (select sum(col2.quantity) qty customerorderlines col2 left join customerorders co2 on co2.id = col2.customerorder_id col2.product_id = 27642 , co2.type = 2) credits col.product_id = 27642 group c.salesrep_id 

so have admit im bit stuck, not being knowledgable mysql @ all.

any suggestions welcome , please feel free point me towards literature on advanced subquerying , joins should reading.

cheers!

i think trick may looking along these lines. in given query/subquery want fix, use sql this, selecting rows, regardless of whether it's invoice or creditnote, , summing value that's either positive or negative depending on way order/money going:

select    sum(case when co.type = 1 col.quantity else -col.quantity end)   ... 

get it? grab order lines back, when sum, you're adding positive credits , negative debits, real total in 1 operation.