I've created a SQLFiddle with my schema and some test data. The calculated columns aren't working for some reason in SQLFiddle, but they come out fine in my MYSQL Workbench. Assume they work correctly.
I've gotten this far with my query:
SELECT DISTINCT o.orderid,i.invoiceid,i.subinvoicenumber,stockingorderid AS oistockingorderid, IF(stockingorderid > 0, 0, po.purchaseorderid) AS purchaseorderid,subponumber,po.paidvia,dropshipfee,i.taxstate, COALESCE( (SELECT mfrname FROM cs_products.tblstockingorders so WHERE so.stockingorderid=oistockingorderid), po.mfrname) AS source, (i.shipping + i.surcharge + (SELECT SUM(additionalshipping * quantity) AS additionalshipping FROM cs_products.tblorderitems WHERE invoiceid=i.invoiceid)) AS shipping, CAST(IF(o.paymentmethod=2, CONCAT('Check: ', i.checknumber),o.paymentmethod) AS CHAR) AS invoicepaidvia, COALESCE( (SELECT (SUM(cost * quantity) * IF(so.mfrdiscount > 0, 1 - so.mfrdiscount, 1)) AS cost FROM cs_products.tblorderitems oi JOIN cs_products.tblallocations a ON oi.orderitemid=a.orderitemid JOIN cs_products.tblstockingorders so ON a.stockingorderid=so.stockingorderid WHERE a.stockingorderid=oistockingorderid), (SELECT (SUM(cost * quantity) * IF(po.mfrdiscount > 0, 1 - po.mfrdiscount, 1)) AS cost FROM cs_products.tblorderitems WHERE purchaseorderid=po.purchaseorderid)) AS grosscost, (SELECT SUM(price * quantity) FROM cs_products.tblorderitems WHERE invoiceid=i.invoiceid) AS grossprice FROM cs_products.tblorders o JOIN cs_products.tblinvoices i ON o.orderid=i.orderid #ordertype of 0 means the order came from the website LEFT JOIN cs_products.tblpurchaseorders po ON o.orderid=po.orderid AND IF(o.ordertype<>0, subinvoicenumber=subponumber, subinvoicenumber=0 AND subponumber>=0) LEFT JOIN cs_products.tblallocations a ON a.orderid=o.orderid
This gets me 95% of the way there. As you can see, for
OrderId 1287, it shows two records with
stockingorderids and none with a
purchaseorderid even though a purchase order has been attributed to 1287. What I'm expecting to see for the missing row is something like:
Again, don't worry about the calculated columns, I just want the record to appear. I feel like I'm missing the smallest thing, but I don't know what it is.