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.

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

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, 
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 
  • 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, 2014 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 COALESCE(sum(pl.quantity), 0) 
        FROM product_locks pl
        WHERE pl.sku = i.sku
    - (
        SELECT COALESCE(sum(oli.quantity), 0) 
            orderlineitems oli
            INNER JOIN responses_authnets ra ON ra.id = oli.response_id 
            oli.sku = i.sku AND ra.salesite_id = i.salesite_id
    ) AS free, 
    i.available AS total, 
FROM product_inventories i
WHERE i.sku in ('sku1','sku2', 'etc...') AND i.salesite_id in ('site_id_1', 'site_id_2', 'etc...')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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