Mysql – Complex MySQL Query returning almost all the correct rows

MySQLquery

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:

1287|276|0|NULL|194|0||0|0|'Quality Fabricators'|357.53|0|781.43|11917.70

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.

Thank you!

Best Answer

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<>0, subinvoicenumber=subponumber, subinvoicenumber=0 AND subponumber>=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<>0, subinvoicenumber=subponumber, subinvoicenumber=0 AND subponumber>=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).

Related Question