Complex MySQL Query returning almost all the correct rows
Something like

SELECT DISTINCT 
o.orderid,
i.invoiceid,
i.subinvoicenumber,
NULL AS oistockingorderid,
po.purchaseorderid,
subponumber,
po.paidvia,
dropshipfee,
i.taxstate
FROM tblorders o 
    JOIN tblinvoices i ON o.orderid=i.orderid
    #ordertype of 0 means the order came from the website
    JOIN tblpurchaseorders po 
      ON o.orderid=po.orderid 
      AND IF(o.ordertype&lt;&gt;0, subinvoicenumber=subponumber, subinvoicenumber=0 AND subponumber&gt;=0)
    LEFT JOIN tblallocations a ON a.orderid=o.orderid

UNION

SELECT DISTINCT 
o.orderid,
i.invoiceid,
i.subinvoicenumber,
stockingorderid AS oistockingorderid,
NULL AS purchaseorderid,
subponumber,
po.paidvia,
dropshipfee,
i.taxstate
FROM tblorders o 
    JOIN tblinvoices i ON o.orderid=i.orderid
    #ordertype of 0 means the order came from the website
    LEFT JOIN tblpurchaseorders po 
      ON o.orderid=po.orderid 
      AND IF(o.ordertype&lt;&gt;0, subinvoicenumber=subponumber, subinvoicenumber=0 AND subponumber&gt;=0)
    LEFT JOIN tblallocations a ON a.orderid=o.orderid


The second SELECT is just the same as your original.
In the calculated fields you can omit the COALESCE from grosscost, and use the two halves separately (according to the non-NULL ID).