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.

My current query only can select one SKU at a time. I can leave salesite_id constant. If there is a way to also have varying salesite_ids that would be good too, but not necessary. Also any suggestions on indexes would be much appreciated also.

SELECT 
    available - (
        SELECT COALESCE(sum(quantity), 0) 
        FROM product_locks 
        WHERE sku = 'sku1'
    ) - (
        SELECT COALESCE(sum(quantity), 0) 
        FROM 
            orderlineitems 
            INNER JOIN responses_authnets ON responses_authnets.id = orderlineitems.response_id 
        WHERE 
            sku = 'sku1' AND responses_authnets.salesite_id = 'site_id_1'
    ) AS free, 
    available AS total, 
    sku, 
    on_hold 
FROM product_inventories 
WHERE sku = 'sku1' AND salesite_id = 'site_id_1';
share|improve this question

2 Answers 2

I have refactored the query to use left joins and IF statement checks for the joins so that you do not have to use nested sub-queries. The group by for sku and site will give the totals by site if needed, but this can be changed to suit your needs.

I am not sure of how the responses_authnets table joins to the orderlineitems for the siteid so this may need additional clarification

SELECT (pi.available - SUM(IF(ISNULL(pl.quantity), 0, pl.quantity)) /* Sums up only those quantities which are not null through the left join */
- SUM (IF(ISNULL(ol.quantity), 0, ol.quantity))) as free, //* SUM up only quantities in the order line items which are not null */
pi.available as total, 
pi.sku,
pi.on_hold
FROM product_inventories pi
LEFT JOIN product_locks pl ON pl.sku = pi.sku /* Left join since not all products in the inventory may have locks */
LEFT JOIN orderlineitems ol ON ol.sku = pi.sku 
LEFT JOIN responses_authnetsid ra ON ra.id = ol.response_id /* Not sure how the responses come up here, so this join is not clear* 
GROUP BY pi.sku, pi.salesite_id 
share|improve this answer
    
If product_inventories has a one-to-many relationship with both product_locks and orderlineitems, then this query will not work, because you will get duplicates to the order of [# of product_locks] * [# of orderlineitems] –  BateTech Feb 11 at 3:05

I'm assuming that product_inventories has a one-to-many relationship with both product_locks and orderlineitems, and that is the reason you are using the subqueries. In that case (and not knowing anything else about the schema), I believe this query should work.

SELECT 
    i.available 
    - (
        SELECT COALESCE(sum(pl.quantity), 0) 
        FROM product_locks pl
        WHERE pl.sku = i.sku
    ) 
    - (
        SELECT COALESCE(sum(oli.quantity), 0) 
        FROM 
            orderlineitems oli
            INNER JOIN responses_authnets ra ON ra.id = oli.response_id 
        WHERE 
            oli.sku = i.sku AND ra.salesite_id = i.salesite_id
    ) AS free, 
    i.available AS total, 
    i.sku, 
    i.on_hold 
FROM product_inventories i
WHERE i.sku in ('sku1','sku2', 'etc...') AND i.salesite_id in ('site_id_1', 'site_id_2', 'etc...')
;
share|improve this answer

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.