MySQL conditional composite join with a subquery (Ecommerce, Sage Accounts Import)

05 Jan 2018

I'm going to try and make the effort to be a bit more forthcoming with useful dev stuff.. stuff that isn't necessarily obvious that I come across from time to time. So to kick that off here's a little tidbit I needed to figure out just now. My e-commerce platform uses separate tables for products, product variations (eg large, small, black, red.. whatever) and product stock. I won't reproduce the tables in full here but essentially consider the 'products' table as a list of primary, or parent products. The 'product_options' table contains all the children, if applicable, of those products with their own SKUs, prices and so on.
Product stock is maintained in a separate table for various reasons, that contains fields for the product ID, option ID (if applicable), current stock level, and fields for tracking stock movement.

In building a tool for importing product stock data from Sage Accounts I needed a query that would give me a single flat array of products with stock levels. The join would be conditional on whether or not a product had child products, and if it did the join would work on composite fields (i.e product ID and option ID). Now there are many ways of skinning the proverbial SQL cat but this is how I did it.. a subquery to get a flat list of products/product variations with the JOIN  condition to the stock table inside a CASE statement.

I have not benchmarked it for performance as I don't really care, the query is run once as an admin task during the parsing and error checking of an imported CSV file that in this case runs to in excess of 20,000 records, in that context a few milliseconds either way is not a worry.

 

SELECT a.*,b.stock 
FROM 
(
    SELECT p.product_id, p.name, p.price,o.option_name, p.sku, o.option_id, o.sku AS option_sku, o.option_price
    FROM products p 
    LEFT JOIN product_options o ON p.product_id = o.parent_id
    WHERE p.deleted = 0 AND (o.deleted = 0 OR o.deleted IS NULL) 
) AS a 
LEFT JOIN product_stock b ON (CASE WHEN a.option_id IS NULL  
                                   THEN b.product_id = a.product_id
                                   ELSE (b.product_id = a.product_id AND a.option_id = b.option_id)
                               END)
ORDER BY a.sku ASC;

 

There. Might come in handy if you have a similar problem especially if you don't necessarily always find SQL syntax completely intuitive.