Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
Do you want to see three records (2 w/ stockingorderid and 1 w/ purchaseorderid) or one with purchaseorderid only? –  dezso May 22 '12 at 10:06
    
@dezso - Three records, 2 w/stockingorderid + no purchaseorderid and 1 w/purchaseorderid + no stockingorderid –  Jason May 22 '12 at 16:04
    
An order can have of multiples of both (PO and SO) linked to one Invoice, or many invoices that have only one PO or SO each –  Jason May 22 '12 at 16:12
    
Are PO and SO mutually exclusive in the desired result set? –  dezso May 22 '12 at 16:26
    
@dezso yes, that is correct. –  Jason May 22 '12 at 17:25
show 7 more comments

1 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).

share|improve this answer
    
i dropped this in my fiddle and got some strange results... are you able to put this in a fiddle? –  Jason May 22 '12 at 18:35
1  
@Jason here you are: sqlfiddle.com/#!2/f25a6/86 what does strange mean? Probably I mistook your idea... –  dezso May 22 '12 at 18:44
    
@Jason - Is there any advance on this? –  dezso May 24 '12 at 6:47
    
yes, quite a bit, actually. i posted on StackOverflow and got some great help there. thank you for your answer, though! –  Jason May 24 '12 at 6:51
    
@Jason - could you then link here that answer as an answer and accept it? Just to keep things clean... –  dezso May 24 '12 at 7:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.