Geek asks and answers

MySQL complex query, I'm so close that I feel it

I created a SQLFiddle (thanks, Amadan !) With my schema and some test data. Computed columns for some reason do not work in SQLFiddle, but in my MYSQL Workbench they work fine. Suppose they work correctly.

I got this far from my request:

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 gives me 95% of the way. As you can see, for OrderId1287 it shows two entries with stockingorderidand none with purchaseorderid, although the purchase order was assigned to 1287. What I expect 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 computed columns, I just want the record to appear. I feel that I am missing the smallest thing, but I do not know what it is.

Thank!

UPDATE , , , . , .

+3
1

, NULL ( 0) stockingorderid purchaseorderid PO SO :

ALTER TABLE tblorderitems MODIFY purchaseorderid int(10) unsigned NULL;
UPDATE tblorderitems SET purchaseorderid = NULL WHERE purchaseorderid = 0;

UNION, (, ) (, ):

  SELECT orderid, stockingorderid, NULL AS purchaseorderid FROM tblallocations
UNION ALL
  SELECT orderid, NULL AS stockingorderid, purchaseorderid FROM tblpurchaseorders

, :

SELECT   orderid, invoiceid, subinvoicenumber, purchaseorderid, subponumber,
         tblpurchaseorders.paidvia, dropshipfee, tblinvoices.taxstate,
         invoicedate, datepaid, stockingorderid,

         COALESCE(tblstockingorders.mfrname, tblpurchaseorders.mfrname) AS source,

         shipping + surcharge + SUM(quantity * additionalshipping) AS shipping,

         CASE paymentmethod
           WHEN 2 THEN CONCAT('Check: ', checknumber)
           ELSE CAST(paymentmethod AS CHAR)
         END AS InvoicePaidVia,

         (1-COALESCE(tblstockingorders.mfrdiscount, tblpurchaseorders.mfrdiscount))
           * SUM(quantity * cost) AS grosscost,

         SUM(quantity * price) AS grossprice

FROM     tblorders
  JOIN   tblinvoices          USING (orderid)
  JOIN (
          SELECT orderid, orderitemid, stockingorderid, NULL AS purchaseorderid
          FROM   tblallocations
        UNION ALL
          SELECT orderid, NULL, NULL, purchaseorderid
          FROM   tblpurchaseorders
       ) AS t                 USING (orderid)
  LEFT JOIN tblpurchaseorders USING (orderid,purchaseorderid)
  LEFT JOIN tblstockingorders USING (stockingorderid)
  LEFT JOIN tblorderitems     USING (orderid,orderitemid,invoiceid,purchaseorderid)
WHERE    ordertype = 0 OR subinvoicenumber = subponumber
GROUP BY orderid, invoiceid, subinvoicenumber, purchaseorderid, subponumber,
         tblpurchaseorders.paidvia, dropshipfee, tblinvoices.taxstate, datepaid,
         invoicedate, stockingorderid, shipping, surcharge, paymentmethod,
         tblstockingorders.mfrname, tblpurchaseorders.mfrname, checknumber,
         tblstockingorders.mfrdiscount, tblpurchaseorders.mfrdiscount

sqlfiddle, invoicedate ( ).

+2

More articles:


All Articles